3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
帮忙写个SQL语句
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 一下
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 2008-01-11
我错了 :(
black205 2008-01-11
呵呵都是高手啊,谢谢楼上的高手们
-狙击手- 2008-01-11
别呀，应该楼上的4,7楼都对的
areswang 2008-01-11
LZ也没大仔细看，海阔天空 的就可以
black205 2008-01-11
谢谢 无枪狙击手,强,高手
-狙击手- 2008-01-11
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 2008-01-11
这样的我也知道啊,我现在的目的是要做成这样的
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列,高手们,这个怎么实现啊?
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列,高手们,这个怎么实现啊?
areswang 2008-01-11
哈哈，LS没大仔细看，顶！
tim_spac 2008-01-11
别名有问题?
select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B.B
from B
left jion A on b.Aid=A.Aid-狙击手- 2008-01-11
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 行）
*/pt1314917 2008-01-11
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
冷箫轻笛 2008-01-11
支持 海阔天空！
看了一楼的，我还以为我看错了呢！呵呵
看了一楼的，我还以为我看错了呢！呵呵
昵称被占用了 2008-01-11
--别名搞错
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
昵称被占用了 2008-01-11
1楼不对吧
昵称被占用了 2008-01-11
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
-狙击手- 2008-01-11
支持LS
tim_spac 2008-01-11
select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B
from B
left jion A on b.Aid=A.Aid