帮忙写个SQL语句

[black205]

A表:
Aid A1 A2
1 a ab
2 b dc

B表:
id Aid B
1 1 c
2 1 ccc
3 1 asdf
4 2 yy
5 2 tet

怎么通过SQL语句实现如下结果:

Aid A1 A2 B
1 a ab c
2 cc
3 asdf
4 b dc yy
5 tet


请高手show 一下

[tim_spac]

select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B
from B
left jion A on b.Aid=A.Aid

[-狙击手-]

支持LS

[昵称被占用了]

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

[昵称被占用了]

1楼不对吧

[昵称被占用了]

--别名搞错
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

[冷箫轻笛]

支持 海阔天空！

看了一楼的，我还以为我看错了呢！呵呵

[pt1314917]

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

[-狙击手-]

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 行）
*/

[tim_spac]

别名有问题?

select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B.B 
from B 
left jion A on b.Aid=A.Aid

[areswang]

哈哈，LS没大仔细看，顶！

[black205]

这样的我也知道啊,我现在的目的是要做成这样的
Aid A1 A2 B
1 a ab c
2 cc
3 asdf
4 b dc yy
5 tet

如第2,3列中A1 ,A2 (a,ab) 不显示出来,只需要在第一列中显示(a,ab),只有当A1,A2的数据发生变化时
才显示出来如第4列,高手们,这个怎么实现啊?

[-狙击手-]

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 行）
*/

[black205]

谢谢 无枪狙击手,强,高手

[areswang]

LZ也没大仔细看，海阔天空 的就可以

[-狙击手-]

别呀，应该楼上的4,7楼都对的

[black205]

呵呵都是高手啊,谢谢楼上的高手们

[tim_spac]

我错了 :(