返回顶部

收藏

用SQL得到全排列

更多

在表Elements中存放了7个数,要得到这些数的全排列。

--创建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

标签:sql,数据库

收藏

0人收藏

支持

0

反对

0

»更多 您可能感兴趣的代码
  1. 2013-07-23 16:18:20Sql Server中Case使用方法 by 自由魂
  2. 2013-08-09 14:03:09命令查看SQL2008中表的索引数据 by YuChao
  3. 2014-02-17 16:06:25Oracle统计表的数据行和数据块信息 by Sycro
  4. 2014-03-15 18:43:08PostgreSQL 列举和停止执行中的 sql by xuleaper
  5. 2014-03-28 16:40:31去除外键的存储过程 by 董一碗
  6. 2014-04-09 20:10:31oracle 回收站管理 by YuChao
  7. 2014-04-25 09:36:11mysql导出csv by 张少华
  8. 2014-05-02 10:44:58复制存储过程 by Ev4n
  9. 2014-05-13 09:55:38删除所有存储过程 by xuleaper
  10. 2014-07-07 21:34:29Oracle 表结构及数据复制 by Merrill
  11. 2014-07-27 10:48:58SQL中的事务处理机制--sp_lock2 by 吴红军

发表评论