【SQL练习】要求编写SQL语句查询(company, agencyid, orderno)组合键值重复的记录明细.

原创于 2025-09-09 13:21:57 发布 · 199 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql #数据库

大数据同时被 3 个专栏收录

277 篇文章

订阅专栏

数据库

124 篇文章

订阅专栏

Oracle

46 篇文章

订阅专栏

要求编写SQL语句查询(company, agencyid, orderno)组合键值重复的记录明细.

在这里插入图片描述

create table tab4(
       company varchar(10),
       agencyid varchar(10),
       orderno varchar(8),
       productid varchar(3),
       producttype1 varchar(2),
       producttype2 varchar(2),
       CONSTRAINT PK_TAB4 PRIMARY KEY (company, agencyid, orderno, productid) 
);

insert into tab4 (company, agencyid, orderno, productid, producttype1, producttype2) 
values ('平安', '100', '1002345', '001', '01', '02');
insert into tab4 (company, agencyid, orderno, productid, producttype1, producttype2) 
values ('平安', '100', '1002345', '002', '01', '02');
insert into tab4 (company, agencyid, orderno, productid, producttype1, producttype2) 
values ('平安', '100', '1002345', '003', '01', '02');
insert into tab4 (company, agencyid, orderno, productid, producttype1, producttype2) 
values ('平安', '120', '1002345', '004', '01', '02');
insert into tab4 (company, agencyid, orderno, productid, producttype1, producttype2) 
values ('平安', '130', '1002345', '001', '01', '02');
commit;

在这里插入图片描述

with temp as
 (select t.company, t.agencyid, t.orderno, count(1)
    from tab4 t
   group by t.company, t.agencyid, t.orderno
  having count(1) >= 2)
select t2.*
  from tab4 t2
 inner join temp
    on t2.company = temp.company
   and t2.agencyid = temp.agencyid
   and t2.orderno = temp.orderno;