全排列

Question

USE tempdb 
GO
IF OBJECT_ID('TEST') IS NOT NULL
  DROP TABLE TEST;
GO
CREATE TABLE TEST
(
  NUM INT,
 );
GO
INSERT INTO TEST VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);
GO
----------------------------------------
SELECT * 
FROM TEST A
JOIN TEST B 
ON A.NUM <> B.NUM
JOIN TEST C
ON A.NUM <> C.NUM AND B.NUM <> C.NUM 
JOIN TEST D
ON A.NUM <> D.NUM AND B.NUM <> D.NUM AND C.NUM <> D.NUM 
JOIN TEST E
ON A.NUM <> E.NUM AND B.NUM <> E.NUM AND C.NUM <> E.NUM AND D.NUM <> E.NUM 
JOIN TEST F
ON A.NUM <> F.NUM AND B.NUM <> F.NUM AND C.NUM <> F.NUM AND D.NUM <> F.NUM AND E.NUM <> F.NUM 
JOIN TEST G
ON A.NUM <> G.NUM AND B.NUM <> G.NUM AND C.NUM <> G.NUM AND D.NUM <> G.NUM AND E.NUM <> G.NUM AND F.NUM <> G.NUM 
JOIN TEST H
ON A.NUM <> H.NUM AND B.NUM <> H.NUM AND C.NUM <> H.NUM AND D.NUM <> H.NUM AND E.NUM <> H.NUM AND F.NUM <> H.NUM AND G.NUM <> H.NUM 
JOIN TEST I
ON A.NUM <> I.NUM AND B.NUM <> I.NUM AND C.NUM <> I.NUM AND D.NUM <> I.NUM AND E.NUM <> I.NUM AND F.NUM <> I.NUM AND G.NUM <> I.NUM AND H.NUM <> I.NUM 
----------------------------------------
/*
结果:全排列 10!=3628800 求优化
*/

Answer 1

declare @a int,@b int,@c int
set @a=1
set @b=1
set @c=1
declare @t table(a int,b int,c int)
while (@a<11)
begin
set @b=1
  while (@b<11)
  begin
  set @c=1
    while (@c<11)  
    begin
    if(@a!=@b and @c!=@b and @a!=@c) and 
    (select count(1) from @t where 
    (convert(varchar,a)+','+convert(varchar,b)+','+convert(varchar,c)) in(
    (convert(varchar,@a)+','+convert(varchar,@c)+','+convert(varchar,@b)),
    (convert(varchar,@b)+','+convert(varchar,@a)+','+convert(varchar,@c)),
    (convert(varchar,@b)+','+convert(varchar,@c)+','+convert(varchar,@a)),
    (convert(varchar,@c)+','+convert(varchar,@a)+','+convert(varchar,@b)),
    (convert(varchar,@c)+','+convert(varchar,@b)+','+convert(varchar,@a))
    ))=0
    insert into @t 
    select @a,@b,@c
    set @c=@c+1
    end
    set @b=@b+1
    end
    set @a=@a+1
    end
    select * from @t
这个可以实现C 10,3 无法实现别的组合，动态的还真不知道用什么办法实现...

Answer 2

返回三百多万数据，优化余地不大，可以使用位运算：
with # as
(
select * from (values(1),(2),(4),(8),(16),(32),(64),(128),(256),(512)) t(n)
)
/*
select * from # a -- C(10,3) = 120
join # b on a.n<b.n
join # c on b.n<c.n
--join # d on c.n<d.n
--join # e on d.n<e.n
--join # f on e.n<f.n
--join # g on f.n<g.n
--join # h on g.n<h.n
--join # i on h.n<i.n
*/
select * from # a -- A(10,3) = 720
join # b on a.n&b.n=0
join # c on a.n|b.n&c.n=0
--join # d on a.n|b.n|c.n&d.n=0
--join # e on a.n|b.n|c.n|d.n&e.n=0
--join # f on a.n|b.n|c.n|d.n|e.n&f.n=0
--join # g on a.n|b.n|c.n|d.n|e.n|f.n&g.n=0
--join # h on a.n|b.n|c.n|d.n|e.n|f.n|g.n&h.n=0
--join # i on a.n|b.n|c.n|d.n|e.n|f.n|g.n|h.n&i.n=0比 AND AND 快点，而且会生成一个串行计划，原来的应该是并行计划。