查询最大值
以下是有关如何解决MySQL常见问题的示例。
一些示例使用该表shop
来保存某些交易商(交易商)的每件商品(商品编号)的价格。假设每个交易者对每件商品有一个固定的价格,则( rticle
,dealer
)是记录的主键。
启动命令行工具mysql并选择一个数据库:
shell>mysql your-database-name
要创建并填充示例表,请使用以下语句:
CREATE TABLE shop ( article INTUNSIGNED DEFAULT '0000' NOT NULL, dealer CHAR(20)DEFAULT '' NOT NULL, price DECIMAL(16,2)DEFAULT '0.00' NOT NULL,PRIMARY KEY ( rticle, dealer));INSERT INTO shopVALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45), (3,'C',1.69),(3,'D',1.25),(4,'D',19.95);
发出语句后,该表应具有以下内容:
SELECT *FROM shopORDER BY article; +--------- +-------- +------- + | article | dealer | price | +--------- +-------- +------- + | 1 | A | 3.45 | | 1 | B | 3.99 | | 2 | A | 10.99 | | 3 | B | 1.45 | | 3 | C | 1.69 | | 3 | D | 1.25 | | 4 | D | 19.95 | +--------- +-------- +------- +
列的最大值
“最高的商品编号是多少?”
SELECT MAX( rticle)AS articleFROM shop; +--------- + | article | +--------- + | 4 | +--------- +
保留某列最大值的行
任务:查找最昂贵商品的数量,经销商和价格。
这可以通过子查询轻松完成:
SELECT article, dealer, priceFROM shopWHERE price=(SELECT MAX(price)FROM shop); +--------- +-------- +------- + | article | dealer | price | +--------- +-------- +------- + | 0004 | D | 19.95 | +--------- +-------- +------- +
其他解决方案是使用LEFT JOIN
或对所有按价格降序的行进行排序,并使用特定于MySQL的LIMIT
子句仅获取第一行:
SELECT s1. rticle, s1.dealer, s1.priceFROM shop s1LEFT JOIN shop s2ON s1.price < s2.priceWHERE s2. rticle IS NULL;SELECT article, dealer, priceFROM shopORDER BY priceDESC LIMIT 1;
注意如果有几本最昂贵的文章,每本的价格为19.95,则
LIMIT
解决方案将仅显示其中之一。
每组最大列数
任务:查找每件商品的最高价格。
SELECT article, MAX(price)AS priceFROM shopGROUP BY articleORDER BY article; +--------- +------- + | article | price | +--------- +------- + | 0001 | 3.99 | | 0002 | 10.99 | | 0003 | 1.69 | | 0004 | 19.95 | +--------- +------- +
保持某一列的分组最大值的行
任务:对于每件商品,找到价格最昂贵的经销商。
可以使用如下子查询来解决此问题:
SELECT article, dealer, priceFROM shop s1WHERE price=(SELECT MAX(s2.price)FROM shop s2WHERE s1. rticle = s2. rticle)ORDER BY article; +--------- +-------- +------- + | article | dealer | price | +--------- +-------- +------- + | 0001 | B | 3.99 | | 0002 | A | 10.99 | | 0003 | C | 1.69 | | 0004 | D | 19.95 | +--------- +-------- +------- +
前面的示例使用了一个相关的子查询,该查询可能效率不高(请参见“相关的子查询”)。解决该问题的其他可能性是在FROM
子句中使用不相关的子查询,a LEFT JOIN
或带有窗口函数的公用表表达式。
不相关的子查询:
SELECT s1. rticle, dealer, s1.priceFROM shop s1JOIN (SELECT article, MAX(price)AS priceFROM shopGROUP BY article)AS s2ON s1. rticle = s2. rticle AND s1.price = s2.priceORDER BY article;
LEFT JOIN
:
SELECT s1. rticle, s1.dealer, s1.priceFROM shop s1LEFT JOIN shop s2ON s1. rticle = s2. rticle AND s1.price < s2.priceWHERE s2. rticle IS NULLORDER BY s1. rticle;
根据以下LEFT JOIN
原理进行工作:当s1.price
处于最大值时,不s2.price
存在更大的值,因此对应的s2.article
值为NULL
。请参见“ JOIN子句”。
带有窗口功能的常用表表达式:
WITH s1AS (SELECT article, dealer, price, RANK()OVER (PARTITION BY articleORDER BY priceDESC )AS `Rank`FROM shop )SELECT article, dealer, priceFROM s1WHERE `Rank` = 1ORDER BY article;