跟大家提一个自动编码的问题
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N 'T '
AND type = 'U ')
DROP TABLE T
GO
CREATE TABLE T (
C1 INT,
C2 CHAR(8),
C3 CHAR(8)
)
GO
INSERT INTO T
SELECT 1, 'AA ', 'K ' UNION ALL
SELECT 2, 'AA ', 'L ' UNION ALL
SELECT 3, 'BB ', 'K ' UNION ALL
SELECT 4, 'BB ', 'L ' UNION ALL
SELECT 5, 'CC ', 'K ' UNION ALL
SELECT 6, 'CC ', 'L ' UNION ALL
SELECT 7, 'DD ', 'K ' UNION ALL
SELECT 8, 'DD ', 'L ' UNION ALL
SELECT 9, 'EE ', 'K ' UNION ALL
SELECT 10, 'FF ', 'L '
现在要得到如下的表,CD为自动编的码:
C1 CD C2 C3
----------- -------- --------
1 101 AA K
2 102 AA L
3 201 BB K
4 202 BB L
5 301 DD K
6 302 DD L
7 401 CC K
8 402 CC L
9 501 EE K
10 601 FF L
（所影响的行数为 10 行）
这里的规律为:C2相同的统统编一个号,如 "AA "为10, "BB "为20, "CC "为40等随机流水编, "K "被定为1, "L "被定为2.如C2=AA,C3=K的,自动编号CD就是:101,请问这个CD这一列怎么算出来?
------解决方案--------------------
IF EXISTS(SELECT name
FROM sysobjects
WHERE name = N 'T '
AND type = 'U ')
DROP TABLE T
GO
CREATE TABLE T (
C1 INT,
C2 CHAR(8),
C3 CHAR(8)
)
GO
INSERT INTO T
SELECT 1, 'AA ', 'K ' UNION ALL
SELECT 2, 'AA ', 'L ' UNION ALL
SELECT 3, 'BB ', 'K ' UNION ALL
SELECT 4, 'BB ', 'L ' UNION ALL
SELECT 5, 'CC ', 'K ' UNION ALL
SELECT 6, 'CC ', 'L ' UNION ALL
SELECT 7, 'DD ', 'K ' UNION ALL
SELECT 8, 'DD ', 'L ' UNION ALL
SELECT 9, 'EE ', 'K ' UNION ALL
SELECT 10, 'FF ', 'L '
go
select * from t
go
select C1,
CD=(select Case C2 when 'AA ' then 10
when 'BB ' then 20
when 'CC ' then 30
when 'DD ' then 40
when 'EE ' then 50
when 'FF ' then 60 end from t t1 where t1.c1=t3.c1 group by C2)*10
+(select Case C3 when 'K ' then 1
when 'L ' then 2 end from t t2 where t2.c1=t3.c1 group by C3),C2,C3 from t t3
C1 CD C2 C3
----------- ----------- -------- --------
1 101 AA K
2 102 AA L
3 201 BB K
4 202 BB L
5 301 CC K
6 302 CC L
7 401 DD K
8 402 DD L
9 501 EE K
10 602 FF L
(10 行受影响)
[解决办法]
select c1,
(select count(distinct c2)+1 from T where c2 <a.c2)*100+(select count(distinct c3)+1 from
T
where
c2=a.c2 and c3 <a.c3) as CD,
c2,c3
from
T a
order by
c2,c3
