22,206
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t TABLE(rowid int IDENTITY(1,1),ddh VARCHAR(10),NAME VARCHAR(10));
INSERT INTO @t(ddh,name) VALUES
('0','n1'),('0','n1'),('0','n1'),('0','n1'),('0','n1'),('0','n1'),('0','n1'),('0','n1'),('0','n1'),('0','n1'),('0','n1')
,('0','n2'),('0','n2'),('0','n2')
,('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3'),('0','n3')
,('0','n4')
SELECT sno=(SELECT RIGHT('0000'+CAST(COUNT(DISTINCT gp) AS VARCHAR(10)),3) FROM
(
SELECT rn1-rn AS gp ,* FROM (
SELECT rn=(SELECT COUNT(1)%10 FROM @t a WHERE a.name=x.NAME AND a.rowid<=x.rowid)
,rn1=(SELECT COUNT(1) FROM @t b WHERE rowid<=x.rowid),* FROM @t x
) w
)u1 WHERE u1.gp<=u.gp
),* FROM (
SELECT rn1-rn AS gp ,* FROM (
SELECT rn=(SELECT COUNT(1)%10 FROM @t a WHERE a.name=x.NAME AND a.rowid<=x.rowid)
,rn1=(SELECT COUNT(1) FROM @t b WHERE rowid<=x.rowid),* FROM @t x
) w
) u
改改应该能在2000下用哈。2000主要是以count来生成序号。