• 首页
  • css3教程
  • html5教程
  • jQuery手册
  • vue手册
  • php手册
  • MySQL手册
  • apache手册
  • redis手册
  • 查询最大值

    以下是有关如何解决MySQL常见问题的示例。

    一些示例使用该表shop来保存某些交易商(交易商)的每件商品(商品编号)的价格。假设每个交易者对每件商品有一个固定的价格,则( rticledealer)是记录的主键。

    启动命令行工具mysql并选择一个数据库:

    shell>mysql your-database-name
    

    要创建并填充示例表,请使用以下语句:

    CREATE TABLE shop (
    article INT UNSIGNED  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 shop VALUES
    (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 shop ORDER 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 article FROM shop;
    
    +---------	+
    | article  |
    +---------	+
    | 4  |
    +---------	+
    

    保留某列最大值的行

    任务:查找最昂贵商品的数量,经销商和价格。

    这可以通过子查询轻松完成:

    SELECT article, dealer, price
    FROM shop
    WHERE price=(SELECT MAX(price) FROM shop);
    
    +---------	+--------	+-------	+
    | article  | dealer  | price  |
    +---------	+--------	+-------	+
    | 0004  | D       | 19.95  |
    +---------	+--------	+-------	+
    

    其他解决方案是使用LEFT JOIN或对所有按价格降序的行进行排序,并使用特定于MySQL的LIMIT子句仅获取第一行:

    SELECT s1. rticle, s1.dealer, s1.price
    FROM shop s1
    LEFT JOIN shop s2 ON s1.price < s2.price
    WHERE s2. rticle IS NULL;
    
    SELECT article, dealer, price
    FROM shop
    ORDER BY price DESC
    LIMIT 1;
    
    注意

    如果有几本最昂贵的文章,每本的价格为19.95,则LIMIT解决方案将仅显示其中之一。


    每组最大列数

    任务:查找每件商品的最高价格。

    SELECT article, MAX(price) AS price
    FROM shop
    GROUP BY article
    ORDER BY article;
    
    +---------	+-------	+
    | article  | price  |
    +---------	+-------	+
    | 0001  | 3.99  |
    | 0002  | 10.99  |
    | 0003  | 1.69  |
    | 0004  | 19.95  |
    +---------	+-------	+
    

    保持某一列的分组最大值的行

    任务:对于每件商品,找到价格最昂贵的经销商。

    可以使用如下子查询来解决此问题:

    SELECT article, dealer, price
    FROM shop s1
    WHERE price=(SELECT MAX(s2.price)
    FROM shop s2
    WHERE 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.price
    FROM shop s1
    JOIN (
    SELECT article, MAX(price) AS price
    FROM shop
    GROUP BY article) AS s2
    ON s1. rticle = s2. rticle AND s1.price = s2.price
    ORDER BY article;
    

    LEFT JOIN

    SELECT s1. rticle, s1.dealer, s1.price
    FROM shop s1
    LEFT JOIN shop s2 ON s1. rticle = s2. rticle AND s1.price < s2.price
    WHERE s2. rticle IS NULL
    ORDER BY s1. rticle;
    

    根据以下LEFT JOIN原理进行工作:当s1.price处于最大值时,不s2.price存在更大的值,因此对应的s2.article值为NULL。请参见“ JOIN子句”。

    带有窗口功能的常用表表达式:

    WITH s1 AS (
    SELECT article, dealer, price,
    RANK() OVER (PARTITION BY article
    ORDER BY price DESC
    ) AS `Rank`
    FROM shop
    )
    SELECT article, dealer, price
    FROM s1
    WHERE `Rank` = 1
    ORDER BY article;