1. Database table initialization

Table mygoods is the commodity table, cat_id is the classification ID, goods_id is the commodity ID, and status is the current status bit of the commodity (1: valid, 0: invalid). The table creation statement is as follows:

CREATE TABLE `mygoods` (  
  `goods_id` int(11) unsigned NOT NULL AUTO_INCREMENT,  
  `cat_id` int(11) NOT NULL DEFAULT '0',  
  `price` tinyint(3) NOT NULL DEFAULT '0',  
  `status` tinyint(3) DEFAULT '1',  
  PRIMARY KEY (`goods_id`),  
  KEY `icatid` (`cat_id`)  
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;  
  
INSERT INTO `mygoods` VALUES (1, 101, 90, 0);  
INSERT INTO `mygoods` VALUES (2, 101, 99, 1);  
INSERT INTO `mygoods` VALUES (3, 102, 98, 0);  
INSERT INTO `mygoods` VALUES (4, 103, 96, 0);  
INSERT INTO `mygoods` VALUES (5, 102, 95, 0);  
INSERT INTO `mygoods` VALUES (6, 102, 94, 1);  
INSERT INTO `mygoods` VALUES (7, 102, 93, 1);  
INSERT INTO `mygoods` VALUES (8, 103, 99, 1);  
INSERT INTO `mygoods` VALUES (9, 103, 98, 1);  
INSERT INTO `mygoods` VALUES (10, 103, 97, 1);  
INSERT INTO `mygoods` VALUES (11, 104, 96, 1);  
INSERT INTO `mygoods` VALUES (12, 104, 95, 1);  
INSERT INTO `mygoods` VALUES (13, 104, 94, 1);  
INSERT INTO `mygoods` VALUES (15, 101, 92, 1);  
INSERT INTO `mygoods` VALUES (16, 101, 93, 1);  
INSERT INTO `mygoods` VALUES (17, 101, 94, 0);  
INSERT INTO `mygoods` VALUES (18, 102, 99, 1);  
INSERT INTO `mygoods` VALUES (19, 105, 85, 1);  
INSERT INTO `mygoods` VALUES (20, 105, 89, 0);  
INSERT INTO `mygoods` VALUES (21, 105, 99, 1);  

2. Find the two commodities with the highest price in each category

Query the two commodities with the highest price in each category. The SQL statement is as follows:

SELECT a.*FROM mygoods a WHERE 
(SELECT count(*) FROM mygoods WHERE cat_id=a.cat_id AND price> a.price)< 2 
ORDER BY a.cat_id,a.price DESC;

The results are as follows:

goods_id	cat_id	price	status
2	101	99	1
17	101	94	0
18	102	99	1
3	102	98	0
8	103	99	1
9	103	98	1
11	104	96	1
12	104	95	1
19	105	85	1

3. Find the two commodities with the highest effective price in each category

Query the two commodities with the highest effective price in each category. The SQL statement is as follows:

SELECT a.*FROM mygoods a WHERE 
(SELECT count(*) FROM mygoods WHERE cat_id=a.cat_id AND price> a.price AND STATUS=1)< 2 
AND STATUS=1 ORDER BY a.cat_id,a.price DESC;

The execution result is shown as follows:

goods_id	cat_id	price	status
2	101	99	1
16	101	93	1
18	102	99	1
6	102	94	1
8	103	99	1
9	103	98	1
11	104	96	1
12	104	95	1
19	105	85	1

4. Find the two commodities with the highest effective price in each category

Query the two commodities with the highest effective price in each category. The SQL statement is as follows:

SELECT a.*FROM mygoods a 
LEFT JOIN mygoods b ON a.cat_id=b.cat_id AND a.price< b.price AND b.STATUS=1 
WHERE a.STATUS=1 GROUP BY a.goods_id,a.cat_id,a.price 
HAVING count(b.goods_id)< 2 ORDER BY a.cat_id,a.price DESC;

The execution result is shown as follows:

goods_id	cat_id	price	status
2	101	99	1
16	101	93	1
18	102	99	1
6	102	94	1
8	103	99	1
9	103	98	1
11	104	96	1
12	104	95	1
19	105	85	1

Conclusion: it can be seen from the above that if conditions need to be added, conditions need to be added in two places.