例如:
id dt
1 2
2 19
3 19
4 11
5 11
6 12实现这样的排序效果
1 1
2 2
3 2
4 3
5 3
6 4即是相同的序号变成一样的.
如何用最优化的算法实现,数据有几十万条.
id dt
1 2
2 19
3 19
4 11
5 11
6 12实现这样的排序效果
1 1
2 2
3 2
4 3
5 3
6 4即是相同的序号变成一样的.
如何用最优化的算法实现,数据有几十万条.
INSERT TB
SELECT 1,2
UNION SELECT 2,19
UNION SELECT 3,19
UNION SELECT 4,11
UNION SELECT 5,11
UNION SELECT 6,12SELECT ID,CNT=ISNULL((SELECT COUNT(A.DT) FROM (SELECT ID=MAX(ID),DT=MAX(DT) FROM TB GROUP BY dt) A WHERE T.ID>A.ID AND T.DT<>A.DT),0)+1
FROM TB TDROP TABLE TB
from 表
INSERT TB
SELECT 1,2
UNION SELECT 2,19
UNION SELECT 3,19
UNION SELECT 4,11
UNION SELECT 5,11
UNION SELECT 6,12union select 7,111
UNION SELECT 8,111
UNION SELECT 9,12
UNION SELECT 10,12
换这个数据就有问题拉。.没有通用性啊
insert @a select 1, 2,null
union all select 2, 19,null
union all select 3, 19,null
union all select 4, 11,null
union all select 5, 11,null
union all select 6, 12,nulldeclare @i int
set @i=0update @a set x=@i,
@i=case when exists(select 1 from @a where id=a.id-1 and dt=a.dt) then @i else @i+1 end
from @a a select * from @a
INSERT TB
SELECT 1,2
UNION SELECT 2,19
UNION SELECT 3,19
UNION SELECT 4,11
UNION SELECT 5,11
UNION SELECT 6,12
UNION SELECT 7,111
UNION SELECT 8,111
UNION SELECT 9,12
UNION SELECT 10,12
UNION SELECT 11,111
UNION SELECT 12,11SELECT A.ID,A.dt,B.CNT FROM TB A INNER JOIN (
SELECT DT,CNT=ISNULL((SELECT COUNT(A.DT) FROM (SELECT ID=MIN(ID),DT FROM TB GROUP BY dt) A WHERE T.ID>=A.ID AND T.DT<>A.DT),0)+1
FROM (SELECT TOP 100 PERCENT ID=MIN(ID),DT FROM TB GROUP BY dt ORDER BY ID) T) B ON A.DT=B.DT
ORDER BY A.IDDROP TABLE TB/*
ID dt CNT
----------- ----------- -----------
1 2 1
2 19 2
3 19 2
4 11 3
5 11 3
6 12 4
7 111 5
8 111 5
9 12 4
10 12 4
11 111 5
12 11 3(12 row(s) affected)R
*/