# 用SQL得到全排列

```--创建Elements表
create table Elements
(
i int not null primary key
)
GO
insert into Elements
values (1),(2),(3),(4),(5),(6),(7);

--[SQL]代码
With ElementsWithWeight as
(
select
i,
power(2,(i-1)) as wgt
from
Elements
)
select
E1.i, E2.i, E3.i, E4.i, E5.i, E6.i, E7.i
from
ElementsWithWeight as E1,
ElementsWithWeight as E2,
ElementsWithWeight as E3,
ElementsWithWeight as E4,
ElementsWithWeight as E5,
ElementsWithWeight as E6,
ElementsWithWeight as E7
where
(E1.wgt + E2.wgt + E3.wgt + E4.wgt + E5.wgt + E6.wgt + E7.wgt) = 127

--初始化数据
create table users (name char(2),value char(1),id number);
insert into users values('甲','a',1);
insert into users values('乙','b',2);
insert into users values('丙','c',3);
insert into users values('丁','d',4);
commit;

--2个元素全排列
select o.p as "排列" from
(select replace (sys_connect_by_path( value, ',' ) , ',' ) p
from users connect by nocycle value != prior value) o
where length(o.p) =2;

--3个元素全排列
select o.p as "排列" from
(select replace (sys_connect_by_path( value, ',' ) , ',' ) p
from users connect by nocycle value != prior value) o
where length(o.p) =3;

--4个元素全排列
select o.p as "排列" from
(select replace (sys_connect_by_path( value, ',' ) , ',' ) p
from users connect by nocycle value != prior value) o
where length(o.p) =4;
--该片段来自于http://outofmemory.cn
```

0人收藏

0

0