-- 建一临时表 create table #tmpA ( [id] UNIQUEIDENTIFIER, [NAME] NVARCHAR(50), [VALUE] INT, flag int ) -- 加入一些数据记录 Insert #tmpA Select NEWID(),'xxx',1,1 Insert #tmpA Select NEWID(),'xxx',1,2 Insert #tmpA Select NEWID(),'xxx',1,3 Insert #tmpA Select NEWID(),'xxx',1,4 Insert #tmpA Select NEWID(),'xxx',0,5 Insert #tmpA Select NEWID(),'xxx',0,6 Insert #tmpA Select NEWID(),'xxx',0,7 Insert #tmpA Select NEWID(),'xxx',0,8 Insert #tmpA Select NEWID(),'zzz',1,9 Insert #tmpA Select NEWID(),'zzz',1,10 Insert #tmpA Select NEWID(),'xxx',1,11 Insert #tmpA Select NEWID(),'xxx',1,12 Insert #tmpA Select NEWID(),'xxx',1,13 Insert #tmpA Select NEWID(),'xxx',1,14DECLARE @table TABLE ( nid INT IDENTITY(1, 1) , [id] UNIQUEIDENTIFIER , [NAME] NVARCHAR(50) , [VALUE] INT , flag INT ) INSERT INTO @table(id,name,value,flag) SELECT * FROM #tmpA SELECT * FROM( SELECT MAX(name) name , MAX(value) value , MAX(flag) flag, MAX(rowid) rowid FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY name, value ORDER BY flag ) AS rowid , * FROM #tmpA ) aa GROUP BY flag - rowid UNION ALL SELECT MAX(name) , MAX(value) , MIN(flag), max(rowid) rowid FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY name, value ORDER BY flag ) AS rowid , * FROM #tmpA ) aa GROUP BY flag - rowid ) bb ORDER BY rowid,flag DROP TABLE #tmpA
xxx 1 1
xxx 1 4xxx 0 5
xxx 0 8zzz 1 9
zzz 1 10xxx 1 11
xxx 1 14
xxx 1 4xxx 0 5
xxx 0 8zzz 1 9
zzz 1 10xxx 1 11
xxx 1 14
create table #tmpA
(
[id] UNIQUEIDENTIFIER,
[NAME] NVARCHAR(50),
[VALUE] INT,
flag int
) -- 加入一些数据记录
Insert #tmpA Select NEWID(),'xxx',1,1
Insert #tmpA Select NEWID(),'xxx',1,2
Insert #tmpA Select NEWID(),'xxx',1,3
Insert #tmpA Select NEWID(),'xxx',1,4
Insert #tmpA Select NEWID(),'xxx',0,5
Insert #tmpA Select NEWID(),'xxx',0,6
Insert #tmpA Select NEWID(),'xxx',0,7
Insert #tmpA Select NEWID(),'xxx',0,8
Insert #tmpA Select NEWID(),'zzz',1,9
Insert #tmpA Select NEWID(),'zzz',1,10
Insert #tmpA Select NEWID(),'xxx',1,11
Insert #tmpA Select NEWID(),'xxx',1,12
Insert #tmpA Select NEWID(),'xxx',1,13
Insert #tmpA Select NEWID(),'xxx',1,14DECLARE @table TABLE
(
nid INT IDENTITY(1, 1) ,
[id] UNIQUEIDENTIFIER ,
[NAME] NVARCHAR(50) ,
[VALUE] INT ,
flag INT
)
INSERT INTO @table(id,name,value,flag)
SELECT * FROM #tmpA
SELECT * FROM(
SELECT MAX(name) name ,
MAX(value) value ,
MAX(flag) flag,
MAX(rowid) rowid
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY name, value ORDER BY flag ) AS rowid ,
*
FROM #tmpA
) aa
GROUP BY flag - rowid
UNION ALL
SELECT MAX(name) ,
MAX(value) ,
MIN(flag),
max(rowid) rowid
FROM ( SELECT ROW_NUMBER() OVER ( PARTITION BY name, value ORDER BY flag ) AS rowid ,
*
FROM #tmpA
) aa
GROUP BY flag - rowid
) bb ORDER BY rowid,flag
DROP TABLE #tmpA
zzz 1 9
zzz 1 10xxx 1 11
xxx 1 14这个不好取LZ说说有什么规律么。?