首頁 >資料庫 >mysql教程 >用SQL得到全排列

用SQL得到全排列

WBOY
WBOY原創
2016-06-07 14:56:531709瀏覽

在表Elements中存放了7个数,要得到这些数的全排列。 无 create table Elements( i int not null primary key)GOinsert into Elementsvalues (1),(2),(3),(4),(5),(6),(7); With ElementsWithWeight as( select i, power(2,(i-1)) as wgt from Elements)selec

在表Elements中存放了7个数,要得到这些数的全排列。
create table Elements
(
    i int not null primary key
)
GO
insert into Elements
values (1),(2),(3),(4),(5),(6),(7);
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;
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;
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;
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;
陳述:
本文內容由網友自願投稿,版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容,請聯絡admin@php.cn