求一条sql 语句?要求如下,大家帮忙!
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,1
insert into @b select 5,1,1
insert into @b select 8,9,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
想要的结果:
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
7 9 1
8 9 1
2 2 4 3 5 2
5 7 2
5 7 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,1
insert into @b select 5,1,1
insert into @b select 8,9,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
想要的结果:
1 2 3 5 1 1
4 5 1
4 5 1
7 7 1
7 9 1
8 9 1
2 2 4 3 5 2
5 7 2
5 7 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这不是一条SQL?
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,1
insert into @b select 5,1,1
insert into @b select 8,9,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 identity(int,1,1) as id ,* into #
from(
select top 100 a.*,b.a2,b.b2
from @a a
right join @b b on a.a1 = b.c2
order by a1,b1) a
select
a3= (case when b1 = (select top 1 b1 from #
where a1 = a.a1 and id < a.id)
then '' else ltrim(a1) end),
b3= (case when b1 = (select top 1 b1 from #
where a1 = a.a1 and id < a.id)
then '' else ltrim(b1) end),
c3= (case when b1 = (select top 1 b1 from #
where a1 = a.a1 and id < a.id)
then '' else ltrim(c1) end)
,a2,b2
from # aorder by id
drop table #/*a3 b3 c3 a2 b2
------------ ------------ ------------ ----------- -----------
1 2 3 5 1
7 9
8 9
7 7
4 5
4 5
2 2 4 5 7
3 5
5 7
3 5
*/
print '1 2 3 5 1 1'
print ' 4 5 1 '.....你直接这样好了,简单明了