求供给种类相同的供应商

有一个供应商表结构如下：

create table tb_vender(
    id number(4,0) not null primary key,
    vendername nvarchar2(20) not null,
    part nvarchar2(20) not null)

充值数据：

insert into tb_vender(id,vendername,part) values('1','创新','cover');
insert into tb_vender(id,vendername,part) values('2','创新','box');
insert into tb_vender(id,vendername,part) values('3','创新','knife');
insert into tb_vender(id,vendername,part) values('4','日立','box');
insert into tb_vender(id,vendername,part) values('5','日立','knife');
insert into tb_vender(id,vendername,part) values('6','渤海','knife');
insert into tb_vender(id,vendername,part) values('7','渤海','box');
insert into tb_vender(id,vendername,part) values('8','渤海','cover');
insert into tb_vender(id,vendername,part) values('9','东风','box');
insert into tb_vender(id,vendername,part) values('10','东风','knife');
insert into tb_vender(id,vendername,part) values('11','益达日平','knife');
insert into tb_vender(id,vendername,part) values('12','益达日平','box');
insert into tb_vender(id,vendername,part) values('13','红日','box');
insert into tb_vender(id,vendername,part) values('14','红日','cover');

充值后数据如下：

SQL> select * from tb_vender;

        ID VENDERNAME                               PART
---------- ---------------------------------------- ----------------------------------------
         1 创新                                     cover
         2 创新                                     box
         3 创新                                     knife
         4 日立                                     box
         5 日立                                     knife
         6 渤海                                     knife
         7 渤海                                     box
         8 渤海                                     cover
         9 东风                                     box
        10 东风                                     knife
        11 益达日平                                 knife
        12 益达日平                                 box
        13 红日                                     box
        14 红日                                     cover

已选择14行。

需求出来了，那就是求供应品种相同的供应商，如创新-渤海，日立-东风等。

有了listagg的帮助，将分组内零件名连接起来不是难事，于是有了下面的SQL：

select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername

这样的数据直接做自联结会出现A-B,B-A的重复情况，于是给它加一列序号。

select tbv.*,rownum as rn from (select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername) tbv

有了上面的SQL，就可以做自联结了：

select a.*,b.* from 
(select tbv.*,rownum as rn from (select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername) tbv) a
inner join
(select tbv.*,rownum as rn from (select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername) tbv) b
on a.parts=b.parts
where a.vname<>b.vname and a.rn<b.rn

上面的SQL中，a.parts=b.parts满足了零件种类个数相同就进行匹配的需求，但这样的数据会有自己和自己连接的情况，因此需要a.vname<>b.vname过滤一次，还有 诸如东风连到日立，后来又有日立连到东风的重复情况，这时就需要a.rn<b,rn确保连接过了的不再连接。

注意on里放连接条件，where里放筛选条件，这样比较清晰易懂。

--2020年3月29日--

上文使用到的全部SQL：

create table tb_vender(
    id number(4,0) not null primary key,
    vendername nvarchar2(20) not null,
    part nvarchar2(20) not null)
    
    

insert into tb_vender(id,vendername,part) values('1','创新','cover');
insert into tb_vender(id,vendername,part) values('2','创新','box');
insert into tb_vender(id,vendername,part) values('3','创新','knife');
insert into tb_vender(id,vendername,part) values('4','日立','box');
insert into tb_vender(id,vendername,part) values('5','日立','knife');
insert into tb_vender(id,vendername,part) values('6','渤海','knife');
insert into tb_vender(id,vendername,part) values('7','渤海','box');
insert into tb_vender(id,vendername,part) values('8','渤海','cover');
insert into tb_vender(id,vendername,part) values('9','东风','box');
insert into tb_vender(id,vendername,part) values('10','东风','knife');
insert into tb_vender(id,vendername,part) values('11','益达日平','knife');
insert into tb_vender(id,vendername,part) values('12','益达日平','box');
insert into tb_vender(id,vendername,part) values('13','红日','box');
insert into tb_vender(id,vendername,part) values('14','红日','cover');


select * from tb_vender

select vendername,count(*) from tb_vender group by vendername

select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername

select tbv.*,rownum as rn from (select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername) tbv

select a.* from 
(select tbv.*,rownum as rn from (select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername) tbv) a

select a.*,b.* from 
(select tbv.*,rownum as rn from (select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername) tbv) a
inner join
(select tbv.*,rownum as rn from (select vendername as vname,listagg(part,',') within group(order by part) as parts from tb_vender group by vendername) tbv) b
on a.parts=b.parts
where a.vname<>b.vname and a.rn<b.rn