用内联结来模拟多人间握手

有一个团队里，一个人需要所有人握手，第二个人需要和除第一个人之外的所有人握手，第三个人需要和第一第二之外的所有人握手，以此类推...

下面将用SQL语句来模拟这一过程。

表结构如下：

create table hy_emp(
    id number(4,0) not null primary key,
    name varchar2(20) not null)

插值：

insert into hy_emp values(52,'Eliot');
insert into hy_emp values(6,'Felix');
insert into hy_emp values(7,'Green');
insert into hy_emp values(86,'Hellen');
insert into hy_emp values(11,'Andy');
insert into hy_emp values(12,'Bill');
insert into hy_emp values(3,'Cindy');
insert into hy_emp values(4,'Douglas');

注意人员的id顺序是打乱的，我们需要按字母序给加上一个列序号。

select row_number() over (order by name asc) as rn,name from hy_emp order by name

然后用内联结模拟握手：

select a.name||' shaked hands with '||b.name
from
(select row_number() over (order by name asc) as rn,name from hy_emp order by name) a
inner join 
(select row_number() over (order by name asc) as rn,name from hy_emp order by name) b
on a.name<>b.name  --连接条件，和除自己外的所有人握手
where a.rn<b.rn  --过滤条件，rn值在前，已经握过手的就不握手了

执行情况：

SQL还可以简化如下：

select a.name||' shaked hands with '||b.name
from
(select row_number() over (order by name asc) as rn,name from hy_emp ) a
inner join 
(select row_number() over (order by name asc) as rn,name from hy_emp ) b
on a.rn<b.rn

执行情况：

本例用到的所有SQL：

create table hy_emp(
    id number(4,0) not null primary key,
    name varchar2(20) not null)
    

insert into hy_emp values(52,'Eliot');
insert into hy_emp values(6,'Felix');
insert into hy_emp values(7,'Green');
insert into hy_emp values(86,'Hellen');
insert into hy_emp values(11,'Andy');
insert into hy_emp values(12,'Bill');
insert into hy_emp values(3,'Cindy');
insert into hy_emp values(4,'Douglas');

commit;

truncate table hy_emp;

select row_number() over (order by name asc) as rn,name from hy_emp order by name

select a.name||' shaked hands with '||b.name
from
(select row_number() over (order by name asc) as rn,name from hy_emp order by name) a
inner join 
(select row_number() over (order by name asc) as rn,name from hy_emp order by name) b
on a.name<>b.name
where a.rn<b.rn

select a.name||' shaked hands with '||b.name
from
(select row_number() over (order by name asc) as rn,name from hy_emp ) a
inner join
(select row_number() over (order by name asc) as rn,name from hy_emp ) b
on a.rn<b.rn

--2020-03-30--