MySQL 中保存某列分组最大值的行
让我们了解如何在MySQL中找到包含特定列的分组最大值的行-
在MySQL中查找包含特定列的分组最大值的行的语法如下：
SELECT colName1, colName2, colName3 FROM tableName s1 WHERE colName3=(SELECT MAX(s2. colName3) FROM tableName s2 WHERE s1. colName1= s2. colName1) ORDER BY colName1;
假设我们有以下产品表-
<产品>
+---------+----------+--------+ | Article | Warehouse| Price | +---------+----------+--------+ | 1 | North | 255.50 | | 1 | North | 256.05 | | 2 | South | 90.50 | | 3 | East | 120.50 | | 3 | East | 123.10 | | 3 | East | 122.10 | +---------+----------+--------|
以下是查询-
询问
SELECT Article, Warehouse, Price FROM Product p1 WHERE Price=(SELECT MAX(p2. Price) FROM Product p2 WHERE p1. Article= p2. Article) ORDER BY Article;输出结果
+-------------+----------------+------------+ | Article | Warehouse | Price | +-------------+----------------+------------+ | 0001 | North | 256.05 | | 0002 | South | 90.50 | | 0003 | East | 123.10 | +-------------+----------------+------------+
上面的查询使用了相关子查询。