MySQL 中保存特定列的分组最大值的行
mysqlmysqli database
让我们了解如何在 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;
假设我们有以下 PRODUCT 表 −
<PRODUCT>
+---------+----------+--------+ | 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 | +-------------+----------------+------------+
上述查询使用了相关子查询。
