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);
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);
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;
![](data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAXMAAACPCAIAAACH/6wTAAAPY0lEQVR4nO2dTZLkqg6FvfRcSs9fvNHdQO2loua5g7wDx3XQCA4yP0ZSnRNEldvGGAnxWWSX08eboihqto7//f8fFhYWlrnl+FAURc3WDbK8Xq/pl1/R5jN6v9/6yn7N1CikK2jUoFHHdcmmzivNXYytaPMxKXv+588f12ZqFNIVNIpk2aOQkdenkK6gUUHIciSq7Ul34rOy7aza8beae4qd74s8C2YW288uXWu26I1Zrqh1puYK3E+NK2oWdRslW5ZXKfoTnNW0pVZn3KgIZHknIVXbyIan+yz5Mzux1mCqkcjbaGbRKBn36UbtrEsTXSEvoffAGxquaXncKDx2xas3z7plOMlSaPOBKZdK7g9DFmxU8SppazWy1C4xnSwaD9RoKE+s7VlNFtCTZlffOjNJFlWbx3+q7QlDlo1m1qKzSZZasL5nu+ItPIDdVTRKGi732CeLcuzeid9IlkKbtVCIR5bidR8jyxvmI+k/lZdY54q7e4KRpVhNjt27NGokSzvO3nUvaza8kKXY4WfIculdic5a46fmuiLtTLEPgEckS/HESyRLvmfWlNseefi6D5ipbKdGlodd0b2BGwdnXRonC+5Vc7w6NkiWQpvZnSrbk22kDpUnpvU1jWftZBcqjtB7+L9a95r5hsnLW8Qo9sZcV0iTi90o+qpoS7O1Ym9HcAkuXTQBnFXrfM1p2c9BoyKQxZ26Iy+eQrqCRpEsexQy8voU0hU0apQsL2qlzkGiXkFdEdioUbIoNXil8uWPX/E1Dl9fX7u7YEUhXUGjpAqrodoHWidZvqfqd5IFfJhXPF3jJS+e5CT0oiVkKcKFZOmWJEtzu1YfqFgtw5YGZH3SrPA/nIR+tIosEi4jZDmO4zeQpWbOCrKAxCelRrahuVyffgNZlONrVvKOAu4xC8mSwaWbLGdT4ckCsoDVqyFQgWSZJf342pQcehwMa8mSwoU5S1OzchZlPpKeBdpMz7p+Tvd8eLJ8nOcs5shywYVkaWoWWZTNpkez3ESeK4NprkgW4zJHFuYseunJoklGms1+/s4+ii1IsjQv16creGobZzUXk7Am12T5VBLY61BW2cHnLCRLbZCU5mPi1LKVa6cMphX5C3MWR9pMliwySJamNJEHEpZaHoF34viQOcuHZOmVd7IUgyE7dMnB37NccyY2WQAaioPUAZHadT8V0Mj9OMEZUXiy3B1fg5ImrDOqTJZiZPAv5bolBwkvcJSVr6OSJpIgq3MWpbxMwluiUVIFstREsnRLroayCuBm0lwuAR+mWLlosi5t0YiT0IsmkGXDo5T/6TiOL4qiTGqULEq91jzr/PP5CV++vr6298FICemKqEYNTe3z18bV0HYPPjNIxf1K8zXVvHgy6iTc3ocVRpEs1ssDZKlVSz+gyf451/nN+MGucF2iGmWdLFccxyYLmK4ryAI+4k27kW2kTZEsHUNc3O/FKBmiOGhNkyUFioRLGLLgGZtGnpIItcabV68dIlkGC8jyXBglh74ZtCTL/qInS61a8U6ioQ+Okuys6+d0z4cnS3FkHRkVjSwAK5HIAkbo5z5ZNG3WoqTWrAymuYVksV+yO1MEshSxEo8s+sjTYwUfzQJF1pRkwSuv7nIGyVF/1rnmCkfFNVkC5iw1rAQjC7CllrMo84haBZytpFeRqyHNdW8V5izGSzSyAKxEIgs2BESexgM1ZNQiBlQjWUYKyWKLLKkCkwWsMvD/OjcXJpqjtcylSJZmmx0lPFnAEtKLUdIEbJRpsjS5s93dD5Ti5yw1BBQDohkutT2P5SzdrghQohpFslgv8u9ZitWKNxMpcFbt0HViETq7XBGmRDVqlCxbnnI+dfBZZ4qyqgk5i0Yvvte5V4ODFEkhXUGjpPavhib4wLyyQQIrneLpxf36nabESehFJIsDSbI0t2v1i/sxpPQIG1Ezfs5qnIRe5IAsVzTHJguYtCvIkl5O04i+Qp/Ck0U/vjZVvLWsM2r/E4kjvbcjPLdXrIbSQ3qyrHN4bLLcGl+bSmmSbXwWGPXoaigwWVJpyIK3AXqyGw6or+nYRJEsXhSKLGesF/eP9N6alIllkyyyWXy5Yqzo25mi2GS5tGLh8KT0seeDLBdHYpPl1DOrIZm8ZJTBFwKX69MZJAff62xbJItj6QdJab4yZ+FqaKmA9xwZFYcsBz/BXfNe53Q/vrrm0LhikwW7zpFRccjyzf91XvZe54wszFnWCfvWkVFZ5+8G7Y0Lnb+WkgVDZ6T3XiQHqQmLW5Ut5CxKOZqEetEoKZLlCcnVUFZB3jo0y6UrVcmakh0At6aHxUnoRRPIsuUp51MHn3WmKKsaJYtSL77XeeCrLrb3wUgJ6YqoRg1N7fPXxtXQdg8+M0jF/Urzi9X0O02VqJNwex9WGEWyWC8ryPIjvjIuU1oNHJ1VmvGDXeG6RDXKB1kOvslMV1lZLWVExpGO1sZLeLIAKHsxCtx1ikY5IMvZ+/Bk0URe9b986mMMvHSXLOscHpss2MMujJImNI2yTpaTKeHJIkcrHSQ80nKYNfRJNwYJNV5IFuOFZPFaJpIF10z3ZxfdlbD8RCcL9qELo6KR5QJKbLLguS0jT4+V2lGZvBTDRZP7TClnkBx8r7Phkg29b7J8//2axKhkkcOWDVKtfnFQZU1wxZ+EKZgazFlGCvCeO6OCkOWCSHHndi+vGDM5SLfqK+sUEyXQIMnSXbDrvBhVTHJrBnoiS+yc5RohZc7y83euoQmIZqAwZ1lUsG+9GCX7D6LFDVlquNnu7gdK8XOWzPaOXMNUztLtigAlqlEki/Ui/56lWK14M5HK6msIBW5NG10RpkQ1apQsW55yPnXwWWeKsqoJOYtGL77XuVeDgxRJIV1Bo6T2r4Ym+MC8skECK53i6cBLR/3LomZ1fq44Cb2IZHEgSZbmdq0+OAS2lQgbUTN+zmqchF5knSxZQEclC563K8giryh/6lsbV2yy3Bpfy9InuQ7Igo+O9N6OsCErVkOYJreamqLwZAFHvRiVxdhSo0iWObpLFrwN0HM3Q9F3clAkiwuFIsul2GQBecei1ZD8ibtBsnTr1vhaVhZv64x69BNcCZcwZEkljdq1GgIXApfr0xkkR+j3Op9qjq9l6WOMZDEn/SApzWfOYkoki+pC5691ZDn4Xuc173XW5CyadmYpNln042tcNUP8keWb73Ve817nLE/5MGdZrLvja1DShHVGPboaik0WIDlI+qQUVP7cuQXhdh6Tl0l4SzRKimR5QnI1lFUANxO8XAKn4PZ3iZPQiyaQZctTzqcOPutMUVY1ShalXnyv88BXXWzvg5ES0hVRjRqa2uevjauh7R58ZpCK+5Xmg2pH/cuitlt9yxWuS1SjSBbr5TGy1LalptvYjB/sCtclqlHWyXJFc2yygEm7giySFPLnyEXvlthkwVz2YpTsPw5a02Q5+CazNe91xjSpOXadw8OTBRx1YZQMDxwwbshSO7rd49OHTZZi5GWnFO8kgD7KDEXfycFCsjgqQchyKTZZcGJZG10wtPhQkS8duc+sEp4sd8fXZklNcE+W4nY8suBBAvWLpzSPalZDmtxnVjmD5Aj9Xmf9+BovQXIWkqWWsyjXMsxZTBWShWR5eqjAIGlOvFVHk7N0XKu7xCZL9/jaKcWUFhtlmizf/F/nNe91zvKUH+Ys9sbXWpEmYKOskwVDZ7u7HyjFz1ky24ErlIf25izdrghQohpFslgv8u9ZitWKNxMp5SlZtWZm9LwrwpSoRo2SZctTzqcOPutMUVY1IWfR6MX3OvdqcJAiKaQraJTU/tXQBB+YVzZIYKVTPB3vP1pfJWdKnIReRLI4kCRLc7tWP90pkaSpCRA2omb8nNU4Cb3IOlmygI5KFjxvV5Dl83fOogfHIp/HI8uh/sZPL0bJ+5A+aG9f6/y1lCzZRlSyZBup1q2GsrOa/lzn8GBkAY6dPgmfkQxRHAzWySIRE5Isl5RkwdsAPRglW7KVU8HI8nnw1TzPSHY7AlmKWIlHlpo5D3zOokl/SJZbikeWLDZwtJAshqQnix4HtWaPytsRlRcCl+vTGSRHoPc6xyNLcbu2h2QxJCVZmvU11YqMwA0yZ7klkmXocucvkmWKNGTpyCOAl1Km9OU+s0SyGJc0YalRJMtM3cpZ9BBhzvK8JKMBtWlU4Vrnr9VkAcQZ6b0XyUG6lVZocpZmzebRZ+RlEt4SjZIiWZ6QXA1lFcDNBC+XrrS2WQ2skp4UJ6EXTSDLlqecTx181pmirGqULEq9+F7nga+62N4HIyWkK6IaNTS1z18bV0PbPfjMIBX3K82vVTvULzCzU6JOwu19WGEUyWK9rCBL9qmKvmZaf2Jpxg92hesS1SjrZLmiOTZZwKR9IGfRg2ORz+OR5VB/46cLo2q3ImCUabIcfCvImvc6F8Oi6c91Dg9GFuBYPL5mi+x20yiSZX/piDx5S0y3lfQBjWhia2IJRhYwpiSLCbJ8czW07L3Os3KfKYVkMV5AbNSC1jRZmLP8PPVeZxxMSvp0lzNIjkDvdQ5GFo1pmVEky/7SEXnX3G46QZOz4CjRHB0szFkcFU3AkCwmSnfkaTwA6qQh0kxGSJZbJRhZpAk4HqyT5Zufsyx7rzNzlidHs2N8rRUZLeBu5IAsGDrb3f1AKX7OktmuSUyahzaSpdsVAUpUo0gW60X+PUuxWvEOKSVP0VRrZkbPuyJMiWrUKFm2POV86uCzzhRlVRNyFo1efK9zrwYHKZJCuoJGSe1fDU3wgXnVBklpfq3aofg2U2viJPQiksWBVpAl+1RFXzOtP1HN+DmrcRJ6kQOyXNEcmyxg0j6Qs+jBUaOPpidA8chyqL/x04VRIEJWGLX/L+VGem9HeEmSDlIxg8BQAGTJTmn6EyQ1+MSmgpEFOBaPr1nhKJL7SRYT6og8cN/Q00dz89EcJVmkfgNZjvo7NkkWE5pLFlzz2jkr98GHlCJZjAvcmVyS5Tv5nCUwWT6VkTslB2k810hjonZ6d+7ToTNIDr7X2YOysPFKlpQjgclySZ+zYC6ABj+CGqAmbkdzokbMWRwpDRj97fDeJc5fq3OW30AWML3BIGk8oCFCLRm52063SBbjAiZ4zVmucC8eGum9KYFZjf/XGbAgrVa73HjOgnmkVDCyFBeMd8fXmmomeM1ZMHRGeu9Fxc9ZNPeNu4e6V0OPycskvCUaJUWyPCH59yzFasU7pJQ8RVNtPCuZIk5CL5pAli1POZ86+KwzRVnVCFn+BayYBeYCHxq/AAAAAElFTkSuQmCC)
该题目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() 等···