怎么计算这个自增列?Using MS SQL, create squence number within a group.
For example, given the groupID, the SQL statement will create the
squence number according to a key.
Input:
GroupID Key
3 a
3 b
3 d
4 b
4 a
5 a
5 e
5 c
5 d
5 b
Output:
GroupID Key SequenceNumber
3 a 1
3 b 2
3 d 3
4 a 1
4 b 2
5 a 1
5 b 2
5 c 3
5 d 4
5 e 5
For example, given the groupID, the SQL statement will create the
squence number according to a key.
Input:
GroupID Key
3 a
3 b
3 d
4 b
4 a
5 a
5 e
5 c
5 d
5 b
Output:
GroupID Key SequenceNumber
3 a 1
3 b 2
3 d 3
4 a 1
4 b 2
5 a 1
5 b 2
5 c 3
5 d 4
5 e 5
SELECT GROUPID,[KEY],ROW_NUMBER() OVER(PARTITION BY GROUPID ORDER BY [KEY]) AS SequenceNumber
FROM TB
SELECT GROUPID,[KEY]
,(SELECT COUNT(1) FROM TB T2 WHERE T2.GROUPID=T1.GROUPID AND T2.[KEY]<=T1.[KEY])
FROM TB T1
IF OBJECT_ID('TEMPDB..#') IS NOT NULL DROP TABLE #
GO
SELECT GROUPID,[KEY],IDENTITY(INT,1,1) AS NID INTO # FROM TB ORDER BY GROUPID,[KEY]
SELECT GROUPID,[KEY]
,(SELECT COUNT(1) FROM # T2 WHERE T2.GROUPID=T1.GROUPID AND T2.[NID]<=T1.[NID])
FROM # T1
DROP TABLE #
insert into #t
select 3,'a' union all
select 3,'b' union all
select 3,'d' union all
select 4,'b' union all
select 4,'a' union all
select 5,'a' union all
select 5,'e' union all
select 5,'c' union all
select 5,'d' union all
select 5,'b' Output:
GroupID Key SequenceNumber
3 a 1
3 b 2
3 d 3
4 a 1
4 b 2
5 a 1
5 b 2
5 c 3
5 d 4
5 e 5sql2005:
select GroupID,key1,row_number() over(partition by GroupID ORDER BY key1) id from #t
insert into #t
select 3,'a' union all
select 3,'b' union all
select 3,'d' union all
select 4,'b' union all
select 4,'a' union all
select 5,'a' union all
select 5,'e' union all
select 5,'c' union all
select 5,'d' union all
select 5,'b' sql2005:
select GroupID,key1,row_number() over(partition by GroupID ORDER BY key1) id from #tsql2000
select GroupID,key1,(select count(*) from #t b where a.GroupID=b.GroupID and ASCII(a.key1)>=ASCII(b.key1)) id
from #t a order by GroupID,key1,id