22,206
社区成员
发帖
与我相关
我的任务
分享
create table #t1(price int)
create table #t2(price int)
insert #t1 select 6
insert #t1 select 7
insert #t1 select 8
insert #t1 select 9
insert #t1 select 10
insert #t1 select 11
insert #t1 select 12
insert #t2 select 6
insert #t2 select 7
insert #t2 select 8
insert #t2 select 9
insert #t2 select 10
insert #t2 select 11
if object_id('tempdb..#data') is not null drop table #data;
;
with list as(
select tb='t1',* from #t1
UNION ALL
select tb='t2',* from #t2
)
select *,rid=ROW_NUMBER()over(order by tb,price )
into #data
from list
select t1.tb+','+cast(t1.price as varchar(50))+';'+
t2.tb+','+cast(t2.price as varchar(50))+';'+
t3.tb+','+cast(t3.price as varchar(50))+';'+
t4.tb+','+cast(t4.price as varchar(50))+';'+
t5.tb+','+cast(t5.price as varchar(50))+';'+
t6.tb+','+cast(t6.price as varchar(50))
from #data t1
inner join #data t2 on t1.rid<t2.rid
inner join #data t3 on t2.rid<t3.rid
inner join #data t4 on t3.rid<t4.rid
inner join #data t5 on t4.rid<t5.rid
inner join #data t6 on t5.rid<t6.rid
/*
结果只显示了部分
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
t1,6;t1,7;t1,8;t1,9;t1,10;t1,11
t1,6;t1,7;t1,8;t1,9;t1,10;t1,12
t1,6;t1,7;t1,8;t1,9;t1,10;t2,6
t1,6;t1,7;t1,8;t1,9;t1,10;t2,7
t1,6;t1,7;t1,8;t1,9;t1,10;t2,8
*/