--表
Declare @t Table(Id Int,SpeID Int,Content Varchar(10))
Insert @t Select 1,1,'aaa'
Union all select 1,1,'aaa'
Union all Select 1,1,'aaa'
Union all select 2,2,'bbb'
Union all Select 2,2,'bbb'--求如下结果
ID SpeID Content
1 1 aaa
1 aaa
1 aaa
2 2 bbb
2 bbb
Declare @t Table(Id Int,SpeID Int,Content Varchar(10))
Insert @t Select 1,1,'aaa'
Union all select 1,1,'aaa'
Union all Select 1,1,'aaa'
Union all select 2,2,'bbb'
Union all Select 2,2,'bbb'--求如下结果
ID SpeID Content
1 1 aaa
1 aaa
1 aaa
2 2 bbb
2 bbb
Declare @t Table(Id Int,SpeID Int,Content Varchar(10))
Insert @t Select 1,1,'aaa'
Union all select 1,1,'aaa'
Union all Select 1,1,'aaa'
Union all select 2,2,'bbb'
Union all Select 2,2,'bbb'-- 查询
SELECT sid = IDENTITY(int, 1, 1), * INTO # FROM @t
SELECT
Id = CASE A.sid
WHEN B.sid THEN RTRIM(A.Id)
ELSE '' END,
A.SpeID, A.Content
FROM # A,
(SELECT sid = MIN(sid), SpeID FROM # GROUP BY SpeID) B
WHERE A.SpeID = B.SpeID
ORDER BY B.sid, A.sidDROP TABLE #--结果
Id SpeID Content
------------ ----------- ----------
1 1 aaa
1 aaa
1 aaa
2 2 bbb
2 bbb(5 行受影响)
Insert @t Select 1,1,'aaa'
Union all select 1,1,'aaa'
Union all Select 1,1,'aaa'
Union all select 2,2,'bbb'
Union all Select 2,2,'bbb'select * from @tdeclare @i int
declare @flg int
set @i = 0
update @t
set @flg = case when id = @i then 0 else id end, id = @flg, @i = idselect case id when 0 then '' else cast(id as varchar) end, speid, content from @t
--表
Declare @t Table(Id Int,SpeID Int,Content Varchar(10))
Insert @t Select 1,1,'aaa'
Union all select 1,1,'aaa'
Union all Select 1,1,'aaa'
Union all select 2,2,'bbb'
Union all Select 2,2,'bbb'-- 查询
;WITH
T AS(
SELECT
sid = ROW_NUMBER() OVER(PARTITION BY Id ORDER BY SpeID),
*
FROM @t
)
SELECT
Id = CASE sid WHEN 1 THEN RTRIM(Id) ELSE '' END, SpeID, Content
FROM T
ORDER BY SpeID, sid--结果
Id SpeID Content
------------ ----------- ----------
1 1 aaa
1 aaa
1 aaa
2 2 bbb
2 bbb(5 行受影响)
Insert @t Select 1,1,'aaa'
Union all select 1,1,'aaa'
Union all Select 1,1,'aaa'
Union all select 2,2,'bbb'
Union all Select 2,2,'bbb'select *,identity(int,1,1) as I into # from @tselect case when id=(select top 1 id from # where a.id=id and a.i>i ) then '' else ltrim(id) end id,
speid,content from # a
drop table #
Declare @t1 Table(i int identity(1,1),Id Int,SpeID Int,Content Varchar(10))
Insert @t Select 1,1,'aaa'
Union all select 1,1,'aaa'
Union all Select 1,1,'aaa'
Union all select 2,2,'bbb'
Union all Select 2,2,'bbb'insert into @t1 select * from (select top 100 percent * from @t t order by id,speid,content)aselect id = isnull(cast(case when i=(select min(i) from @t1 where id=t.id and speid=t.speid and content=t.content) then id else null end as varchar(5)),'') ,
speid,content from @t1 t(所影响的行数为 5 行)id speid content
----- ----------- ----------
1 1 aaa
1 aaa
1 aaa
2 2 bbb
2 bbb(所影响的行数为 5 行)