22,209
社区成员
发帖
与我相关
我的任务
分享
declare @TempTable table ( c1 int , c2 int , c3 int )
insert into @TempTable
( c1 , c2 , c3 )
values
( 1 , 2 , 4 ),
( 2 , 4 , 7 ),
( 2 , 6 , 9 ),
( 3 , 9 , 0 ),
( 1 , 6 , 7 ),
( 2 , 9 , 5 ) ;
with T1
as ( select distinct
DENSE_RANK() over ( order by c1 ) as cIndex ,
c1
from
@TempTable
),
T2
as ( select distinct
DENSE_RANK() over ( order by c2 ) as cIndex ,
c2
from
@TempTable
),
T3
as ( select distinct
DENSE_RANK() over ( order by c3 ) as cIndex ,
c3
from
@TempTable
)
--select * from t2
select
c1 ,
c2 ,
c3
from
master.dbo.spt_values
left join T1
on t1.cIndex = dbo.spt_values.number
left join T2
on t2.cIndex = dbo.spt_values.number
left join T3
on t3.cIndex = dbo.spt_values.number
where
type = 'P'
and dbo.spt_values.number between 1
and ( select
max(num)
from
( select
count(1) num
from
t1
union all
select
count(1)
from
t2
union all
select
count(1)
from
t3
) TT
)