大家帮我看看下面的语句:要求是
declare @a table (a1 int,b1 int,c1 int)
insert into @a select 1,2,3
insert into @a select 2,2,4
insert into @a select 3,6,7
declare @b table (a2 int,b2 int,c2 int)
insert into @b select 7,9.000000,1
insert into @b select 5,1,1
insert into @b select 8,9.000000,1
insert into @b select 3,5,2
insert into @b select 5,7,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,7,1
insert into @b select 3,5,2
insert into @b select 5,7,2
select
[a1]=case when not exists(select * from @b where c2=b.c2 and b2 <b.b2) then rtrim(a.a1) else '' end,
[b1]=case when not exists(select * from @b where c2=b.c2 and b2 <b.b2) then rtrim(a.b1) else '' end,
[c1]=case when not exists(select * from @b where c2=b.c2 and b2 <b.b2) then rtrim(a.c1) else '' end,
b.*
from
@a a
inner join
@b b on a.a1=b.c2
order by b.c2,b.b2
要求是:结果是
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
8 9 1
7 9 1
2 2 4 9 5 2
5 5 2
3 5 2
7 5 2
declare @a table (a1 int,b1 int,c1 int)
insert into @a select 1,2,3
insert into @a select 2,2,4
insert into @a select 3,6,7
declare @b table (a2 int,b2 int,c2 int)
insert into @b select 7,9.000000,1
insert into @b select 5,1,1
insert into @b select 8,9.000000,1
insert into @b select 3,5,2
insert into @b select 5,7,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,7,1
insert into @b select 3,5,2
insert into @b select 5,7,2
select
[a1]=case when not exists(select * from @b where c2=b.c2 and b2 <b.b2) then rtrim(a.a1) else '' end,
[b1]=case when not exists(select * from @b where c2=b.c2 and b2 <b.b2) then rtrim(a.b1) else '' end,
[c1]=case when not exists(select * from @b where c2=b.c2 and b2 <b.b2) then rtrim(a.c1) else '' end,
b.*
from
@a a
inner join
@b b on a.a1=b.c2
order by b.c2,b.b2
要求是:结果是
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
8 9 1
7 9 1
2 2 4 9 5 2
5 5 2
3 5 2
7 5 2
a1 b1 c1 a2 b2 c2
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
8 9 1
7 9 1
2 2 4 9 5 2
5 5 2
3 5 2
7 5 2 不好意思!没排版
a1 b1 c1 a2 b2 c2
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
8 9 1
7 9 1
2 2 4 9 5 2
5 5 2
3 5 2
7 5 2 不好意思!没排版
declare @a table (a1 int,b1 int,c1 int)
insert into @a select 1,2,3
insert into @a select 2,2,4
insert into @a select 3,6,7
declare @b table (a2 int,b2 int,c2 int)
insert into @b select 7,9.000000,1
insert into @b select 5,1,1
insert into @b select 8,9.000000,1
insert into @b select 3,5,2
insert into @b select 5,7,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,7,1
insert into @b select 3,5,2
insert into @b select 5,7,2 select * from
(select distinct cast(a1 as varchar)a1,cast(b1 as varchar)b1,cast(c1 as varchar)c1,a2,b2,c2 from @a,(select * from @b b where not exists(select 1 from @b where c2=b.c2 and b2<b.b2))d
where a1=c2
union all
select '','','',a2,b2,c2 from @a,(select * from @b b where exists(select 1 from @b where c2=b.c2 and b2<b.b2))d
where a1=c2)c order by c2,a1 desc
insert into @a select 1,2,3
insert into @a select 2,2,4
insert into @a select 3,6,7
declare @b table (a2 int,b2 int,c2 int)
insert into @b select 7,9.000000,1
insert into @b select 5,1,1
insert into @b select 8,9.000000,1
insert into @b select 3,5,2
insert into @b select 5,7,2
insert into @b select 4,5,1
insert into @b select 4,5,1
insert into @b select 7,7,1
insert into @b select 3,5,2
insert into @b select 5,7,2 要求是:结果是
a1 b1 c1 a2 b2 c2
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
8 9 1
7 9 1
2 2 4 9 5 2
5 5 2
3 5 2
7 5 2
a1 b1 c1 a2 b2 c2
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
8 9 1
7 9 1
2 2 4 9 5 2
5 5 2
3 5 2
7 5 2