;with maco as
(
    select right('0'+ltrim(number),2) as c
    from master..spt_values where type='p' and number between 1 and 10
)

select * from maco a1,maco a2,maco a3,maco a4,maco a5,maco a6,maco a7
where 
a1.c<>a2.c and a1.c<>a3.c and a1.c<>a4.c and a1.c<>a5.c and a1.c<>a6.c and a1.c<>a7.c
and a2.c<>a3.c and a2.c<>a4.c and a2.c<>a5.c and a2.c<>a6.c and a2.c<>a7.c
and a3.c<>a4.c and a3.c<>a5.c and a3.c<>a6.c and a3.c<>a7.c 
and a4.c<>a5.c and a4.c<>a6.c and a4.c<>a7.c
and a5.c<>a6.c and a5.c<>a7.c
and a6.c<>a7.c
/*

c    c    c    c    c    c    c
---- ---- ---- ---- ---- ---- ----
04   07   06   05   01   03   02
04   08   06   05   01   03   02
04   09   06   05   01   03   02
04   10   06   05   01   03   02
04   06   07   05   01   03   02
04   08   07   05   01   03   02
04   09   07   05   01   03   02
04   10   07   05   01   03   02
...
07   01   04   06   10   08   09
07   02   04   06   10   08   09
07   03   04   06   10   08   09
07   05   04   06   10   08   09
07   01   05   06   10   08   09
07   02   05   06   10   08   09
07   03   05   06   10   08   09
07   04   05   06   10   08   09

(604800 row(s) affected)
*/

--运行了8秒钟

------解决方案--------------------
探讨


SQL code

;with maco as
(
    select right('0'+ltrim(number),2) as c
    from master..spt_values where type='p' and number between 1 and 10
)

select * from maco a1,maco a2,maco a3,maco a4,maco a5,m……


------解决方案--------------------

SQL code

set nocount on
;with maco as
(
    select right('0'+ltrim(number),2) as c
    from master..spt_values where type='p' and number between 1 and 10
)
,t1 as
(
select a1.c as c1,a2.c as c2 from maco a1,maco a2 where a1.c<a2.c
),t2 as
(
select a1.c1 as c1,a1.c2 as c2,a2.c1 as c3,a2.c2 as c4 
from t1 a1,t1 a2 where a1.c1>a2.c1 and a1.c2>a2.c1 and a1.c1>a2.c2 and a1.c2>a2.c2
),t3 as 
(
select a1.*,a2.c1 as c5,a2.c2 as c6 from t2 a1,t1 a2 
where a1.c1<a2.c1 and a1.c2<a2.c1 and a1.c3<a2.c1 and a1.c4<a2.c1
and  a1.c1<a2.c2 and a1.c2<a2.c2 and a1.c3<a2.c2 and a1.c4<a2.c2
),t4 as 
(
select a1.*,a2.c as c7 from t3 a1,maco a2 
where a1.c1<a2.c and a1.c2<a2.c and a1.c3<a2.c and a1.c4<a2.c
and a1.c5<a2.c and a1.c6<a2.c 
)
select c3,c4,c1,c2,c5,c6,c7 from t4 order by 1,2,3,4,5,6,7