A表:
Aid A1 A2
1 a ab
2 b dcB表:
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 dcB表:
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 一下
解决方案 »
- 快速插入结果的问题
- 字符截取问题
- *** 高分请教:关于SQLServer的同步和复制的问题 ***
- 不能对包含聚合或子查询的表达式执行聚合函数
- 问题简述:根据字段内以,分隔的内容来排序出结果,求SQL语句,请大家帮忙~~~
- 怎样在IE6中显示xml文件?
- 高人快来救救我,我快疯了,目前电信营业厅客户端到底是winform的,还是web的啊?
- 对※一条※记录的循环处理
- -----存储过程中如何定义一个一维的数组?如何往这个数组中付值?如何循环?
- 问个基本功问题。。。关于删除表内某行的
- 2005转成2000
- A cursor with the name 'NEXT' does not exist.环境 sqlserver
select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B
from B
left jion A on b.Aid=A.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 a.Aid=b.Aid
order by b.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 a1.Aid=b1.Aid
order by b1.id
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
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 行)
*/
select b.Aid, A1=isnull(A1,''), A2=isnull(A2,''), B.B
from B
left jion A on b.Aid=A.Aid
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列,高手们,这个怎么实现啊?
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 行)
*/