Be asked a question on one side
And I wrote a SQL without thinking.
The query results are correct for this topic, but the thought that there might be a parentid top clicknum is the same as another parentid non-top clicknum so that the query results will contain this "another parentid non-top clicknum "Practice the following: Table creates the CREATE table ProductInfo (
ProductID int,productname varchar2 (255), parentid int,clicknum int); Initialize table information
Insert into ProductInfo (Productid,productname,parentid,clicknum) VALUES (1, ' Men's clothes ', 1,90);
Insert into ProductInfo (Productid,productname,parentid,clicknum) VALUES (2, ' Ladies Clothes ', 1,80);
Insert into ProductInfo (Productid,productname,parentid,clicknum) VALUES (3, ' Men's trousers ', 2,70);
Insert into ProductInfo (productid,productname,parentid,clicknum) VALUES (4, ' Ladies Pants ', 2,90);
Insert into ProductInfo (Productid,productname,parentid,clicknum) VALUES (5, ' Men's hats ', 5,15);
Insert into ProductInfo (Productid,productname,parentid,clicknum) VALUES (6, ' Ladies ' hats ', 5,30);
Insert into ProductInfo (productid,productname,parentid,clicknum) VALUES (7, ' Men's Shoes ', 10,65);
Insert into ProductInfo (Productid,productname,parentid,clicknum) VALUES (8, ' ladies shoes ', 10,52);
Insert into ProductInfo (Productid,productname,parentid,clicknum) VALUES (9, ' ladies shoes 1 ', 10,54); Query table information SELECT * FROM ProductInfo;
The topic Sqlselect * from ProductInfo where Clicknum in (select Max (clicknum) from ProductInfo Group by ParentID) Order by click Num desc; So it looks right. Add a special record insert into ProductInfo (productid,productname,parentid,clicknum) VALUES (10, ' ladies shoes 1 ', 10, 30);
Query again for SELECT * from ProductInfo where Clicknum in (select Max (clicknum) from ProductInfo Group by ParentID) Order by ClickN Um desc; Results This record is not supposed to occur using in greedy match query again select * from ProductInfo where (Clicknum,parentid) in (select Max (clicknum), parent ID from ProductInfo GROUP by ParentID) Order BY clicknum Desc
As a result, the discovery of the ParentID top clicknum in the same special case as another parentid non-top clicknum: Use GROUP by when select Max (Clicknum), ParentID from PR Oductinfo GROUP BY Parentidclicknum is not possible in the group by collection to remove Max (), an error is when using GROUP by when the select content can be a group by subset + aggregate function Max (), Su M (), ARV (), COUNT () etc. ...
Oracle in greedy matching and group by conditions
