34,838
社区成员




declare @ta table(Aid int,A1 char(1),A2 char(2))
insert @ta select
1 ,'a' ,'ab' union select
2 ,'b' ,'dc'
declare @tb table(id int,Aid int,B varchar(10))
insert @tb select
1 , 1 ,'c' union select
2 , 1 ,'ccc' union select
3 , 1 ,'asdf' union select
4 , 2 ,'yy' union select
5 , 2 ,'tet'
select b.id,
A1= case when b.aid = (select top 1 aid from @tb where aid = b.aid and id <b.id) then '' else a1 end,
A2= case when b.aid = (select top 1 aid from @tb where aid = b.aid and id <b.id) then '' else a2 end,
B
from @tb b
left join @ta a on b.Aid=A.Aid
/*
id A1 A2 B
----------- ---- ---- ----------
1 a ab c
2 ccc
3 asdf
4 b dc yy
5 tet
(所影响的行数为 5 行)
*/
select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B.B
from B
left jion A on b.Aid=A.Aid
declare @ta table(Aid int,A1 char(1),A2 char(2))
insert @ta select
1 ,'a' ,'ab' union select
2 ,'b' ,'dc'
declare @tb table(id int,Aid int,B varchar(10))
insert @tb select
1 , 1 ,'c' union select
2 , 1 ,'ccc' union select
3 , 1 ,'asdf' union select
4 , 2 ,'yy' union select
5 , 2 ,'tet'
select b.Aid,
A1= case when b.aid = (select top 1 aid from @tb where aid = b.aid and id <b.id) then '' else a1 end,
A2= case when b.aid = (select top 1 aid from @tb where aid = b.aid and id <b.id) then '' else a2 end,
B
from @tb b
left join @ta a on b.Aid=A.Aid
/*
Aid A1 A2 B
----------- ---- ---- ----------
1 a ab c
1 ccc
1 asdf
2 b dc yy
2 tet
(所影响的行数为 5 行)
*/
declare @a table (Aid int,a1 varchar(10),a2 varchar(10))
insert into @a select 1,'a','ab'
insert into @a select 2,'b','dc'
declare @b table (id int,Aid int,B varchar(10))
insert into @b select 1,1,'c'
insert into @b select 2,1,'ccc'
insert into @b select 3,1,'asdf'
insert into @b select 4,2,'yy'
insert into @b select 5,2,'tet'
select * from
(select id Aid,a1,a2,B from @a a,(select * from @b b where not exists(select 1 from @b where Aid=b.Aid and id<b.id))d
where a.Aid=d.Aid
union all
select id Aid,'','',B from @a a,(select * from @b b where exists(select 1 from @b where Aid=b.Aid and id<b.id))d
where a.Aid=d.Aid)c order by Aid
--别名搞错
select b1.id as Aid,
case when exists (select 1 from B where Aid=b1.Aid and id<b1.id) then '' else a1.A1 end as A1,
case when exists (select 1 from B where Aid=b1.Aid and id<b1.id) then '' else a1.A2 end as A2,
b1.B
from a a1,b b1
where a1.Aid=b1.Aid
order by b1.id
select b1.id as Aid,
case when exists (select 1 from B where Aid=b1.Aid and id<b1.id) then '' else a1.A1 end as A1,
case when exists (select 1 from B where Aid=b1.Aid and id<b1.id) then '' else a1.A2 end as A2,
b1.B
from a a1,b b1
where a.Aid=b.Aid
order by b.id
select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B
from B
left jion A on b.Aid=A.Aid