Oracle in 贪婪匹配 与 group by 条件

被问到一面试题

不假思索地写了个sql

针对这道题目来讲查询结果是正确的，但是想到可能存在一种parentID的top clickNum与另一种parentID非 top clickNum相同的情况

这样查询结果会包含这 【另一种parentID非 top clickNum】

 

实践了下：

 

表创建

create table productInfo (
productID int,productName varchar2(255),parentID int,clickNum int);

初始化表信息
insert into productInfo (productID,productName,parentID,clickNum) values (1,'男士衣服',1,90);
insert into productInfo (productID,productName,parentID,clickNum) values (2,'女士衣服',1,80);
insert into productInfo (productID,productName,parentID,clickNum) values (3,'男士裤子',2,70);
insert into productInfo (productID,productName,parentID,clickNum) values (4,'女士裤子',2,90);
insert into productInfo (productID,productName,parentID,clickNum) values (5,'男士帽子',5,15);
insert into productInfo (productID,productName,parentID,clickNum) values (6,'女士帽子',5,30);
insert into productInfo (productID,productName,parentID,clickNum) values (7,'男士鞋子',10,65);
insert into productInfo (productID,productName,parentID,clickNum) values (8,'女士鞋子',10,52);
insert into productInfo (productID,productName,parentID,clickNum) values (9,'女士鞋子1',10,54);

查询表信息

select * from productInfo;

该题目SQL

select * from productinfo where clicknum in (select max(clickNum) from productinfo group by parentid) order by clicknum desc;

结果

   这样看来是正确的

 

新增一条特殊的记录

insert into productInfo (productID,productName,parentID,clickNum) values (10,'女士鞋子1',10,30);
再次查询

select * from productinfo where clicknum in (select max(clickNum) from productinfo group by parentid) order by clicknum desc;

结果

这条记录是不应该出现的

 

 

使用 in 贪婪匹配

再次查询

select * from productinfo where (clickNum,parentID) in (select max(clickNum),parentID from productinfo group by parentid) order by clicknum desc
结果

这样就去除了 parentID的top clickNum与另一种parentID非 top clickNum相同的特殊情况

 

 

过程中的发现：

使用 group by 时

select max(clickNum),parentID from productinfo group by parentid

clickNum并未在group by集合中是可以的

去掉max()，报错

 

 

即使用group by 时

select 内容  可以是 group by 子集 + 聚合函数 max(),sum(),arv(),count() 等···