导航
  • 主页
  • 基础类
  • 应用实例
  • 新技术前沿

帮忙写个SQL语句

black205 2008-01-11 10:34:31
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 一下

...全文
110 点赞 收藏 17
写回复
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
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列,高手们,这个怎么实现啊?
回复
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
支持 海阔天空!

看了一楼的,我还以为我看错了呢!呵呵
回复

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


回复
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 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
回复
发动态
发帖子
MS-SQL Server
创建于2007-09-28

3.2w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
社区公告
暂无公告