declare @T table(Name nvarchar(2),C1 int,C2 int)
insert @T select 'A', 1 , 2
insert @T select 'A', 1 , 3
insert @T select 'A', 2 , 3
insert @T select 'B', 1 , 2
insert @T select 'C', 1 , 2
select * ,ID=identity(int,1,1) into # from @Tgoselect
[Name]=case when not exists(select 1 from # where Name=t.Name and ID<t.ID) then Name else '' end,
c1,
c2
from
# tName c1 c2
---- ----------- -----------
A 1 2
1 3
2 3
B 1 2
C 1 2(所影响的行数为 5 行)
insert @T select 'A', 1 , 2
insert @T select 'A', 1 , 3
insert @T select 'A', 2 , 3
insert @T select 'B', 1 , 2
insert @T select 'C', 1 , 2
select * ,ID=identity(int,1,1) into # from @Tgoselect
[Name]=case when not exists(select 1 from # where Name=t.Name and ID<t.ID) then Name else '' end,
c1,
c2
from
# tName c1 c2
---- ----------- -----------
A 1 2
1 3
2 3
B 1 2
C 1 2(所影响的行数为 5 行)
insert tb select 'A', 1, 2
union all select 'A', 1, 3
union all select 'A', 2, 3
union all select 'B', 1, 2
union all select 'C', 1, 2goselect *, nId=identity(int,1,1) into #t from tb
select Name, C1, C2 from
(
select Name, C1, C2, TheOrder=Name,nId from #t t where not exists (select 1 from #t where Name=t.Name and nId<t.nId)
union all
select '', C1,C2, Name,nId from #t t where exists (select 1 from #t where Name=t.Name and nId<t.nId)
) T
order by TheOrder,nIdgo
drop table #t
drop table tb/*
Name C1 C2
---------------- ----------- -----------
A 1 2
1 3
2 3
B 1 2
C 1 2(5 row(s) affected)
*/
insert into tb values('A', 1, 2)
insert into tb values('A', 1, 3)
insert into tb values('A', 2, 3)
insert into tb values('B', 1, 2)
insert into tb values('C', 1, 2)
goselect case when c1=(select top 1 c1 from tb where name = a.name) then name else '' end as name,c1,c2 from tb adrop table tb/*
name c1 c2
---------- ----------- -----------
A 1 2
A 1 3
2 3
B 1 2
C 1 2(所影响的行数为 5 行)
*/