MySQL SELECT查询返回错误的结果

Question

我有一张orders(c_id(INT),o_date(DATE),o_price(INT))桌子。我必须找出本年度每个id的总价格的id和MAX值。如下表所示：

c_id    o_date     o_price
   1    2017-08-27      30    
   2    2017-05-25     100    
   2    2017-05-02      20    
   1    2017-02-23      80    
   3    2017-01-26      60   
   4    2016-04-22      10    
   2    2016-03-15       5   
   1    2015-09-01       1

我的代码如下：

SELECT c_id, MAX(m_price)  
FROM (  SELECT c_id, SUM(o_price) AS
 m_price FROM orders WHERE  
(       YEAR(o_date) = YEAR(curdate())  ) 
GROUP
> BY c_id )AS T

最大值返回右侧，但id与最大值不匹配。有没有人对我的情况有什么建议？提前谢谢你！！

Answer 1

如果保证只有一个c_id具有最大的和，或者您不介意在平局时只返回一个结果，那么您可以只使用LIMIT 1和一个ORDER BY来获得最大结果。

SELECT
    c_id,
    SUM(o_price) AS m_price
FROM orders
WHERE
    YEAR(o_date) = YEAR(CURDATE())
GROUP BY c_id
ORDER BY m_price DESC
LIMIT 1

如果可以有一个最大和的平局，那么就有一些稍微丑陋的变通方法，例如

SELECT
    c_id,
    SUM(o_price) AS m_price
FROM orders
WHERE
    YEAR(o_date) = YEAR(CURDATE()) AND
GROUP BY c_id
HAVING SUM(o_price) =
(
    SELECT MAX(t.m_price)
    FROM
    (SELECT SUM(o_price) AS m_price FROM orders
     WHERE YEAR(o_date) = YEAR(CURDATE()) GROUP BY c_id) t
)

上面的查询有点冗长，在其他数据库中，我们会使用分析函数来处理这一点。但是由于MySQL不支持这些开箱即用的方式，我们需要使用另一种方式。

Answer 2

如果你有多个价格，下面的代码比Tim的代码稍微简单一点：

SELECT o.c_id, SUM(o.o_price) AS m_price
FROM orders o
WHERE YEAR(o.o_date) = YEAR(CURDATE())
GROUP BY o.c_id
HAVING m_price = (SELECT SUM(o2.o_price)
                  FROM orders o2
                  WHERE YEAR(o2.o_date) = YEAR(CURDATE()
                  GROUP BY c_id
                  ORDER BY SUM(o2.o_price) DESC
                  LIMIT 1
                 );

在实践中，使用变量可能会更快。

SELECT o.*
FROM (SELECT o.*, @maxp := GREATEST(@maxp, m_price)
      FROM (SELECT o.c_id, SUM(o.o_price) AS m_price
            FROM orders o
            WHERE YEAR(o.o_date) = YEAR(CURDATE())
            GROUP BY o.c_id
           ) o CROSS JOIN
           (SELECT @maxp := 0) params
     ) o
WHERE m_price = @maxp;

Answer 3

将查询调整为

 SELECT c_id, MAX(m_price)  
    FROM (  SELECT c_id, SUM(o_price) AS
     m_price FROM orders WHERE  
     YEAR(o_date) = YEAR(curdate())
    ) 
    GROUP BY c_id