时间戳

首页 > 解决方案 > 如何编写 SQL 聚合函数/查询

sql - 如何编写 SQL 聚合函数/查询

问题描述

我有一个查询，显示每天的总值（金额总和）。查询：

SELECT CAST(date AS DATE), SUM(amount) AS total_amount FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)

CAST 是将日期时间格式缩写为日期。

现在我只想用 max 函数选择总和最高的那一天。为此，我尝试编写以下聚合查询：

SELECT s.date, s.total_amount
FROM (SELECT CAST(date AS DATE), SUM(amount) AS total_amount FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)) s
WHERE s.total_amount = (SELECT MAX(s.total_amount) FROM table)

这不起作用。我知道问题出在最后的 WHERE 子句上，但我需要帮助才能使它工作。

标签： sqlaggregate-functions

解决方案

ORDER BY与 一起使用LIMIT：

SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)
ORDER BY total_amount DESC
LIMIT 1;

如果您正在使用，SQL Server那么您可以使用TOP：

SELECT TOP (1) CAST(date AS DATE), SUM(amount) AS total_amount 
FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)
ORDER BY total_amount DESC;

如果你想要领带，那么你可以使用窗口功能：

SELECT t.*
FROM (SELECT CAST(date AS DATE), SUM(amount) AS total_amount,
             RANK() OVER (ORDER BY SUM(amount) DESC) as Seq
      FROM table
      WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
      GROUP BY CAST(date AS DATE)
     ) t
WHERE seq = 1;

您可以使用CTE：

WITH CTE AS (
     SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
     FROM table
     WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
     GROUP BY CAST(date AS DATE)
)  
SELECT c.*
FROM CTE C
WHERE C.total_amount = (SELECT MAX(total_amount) FROM CTE);

注意：如果您的 DBMS 不支持CTE表达式，那么您需要在子查询中重复该SELECT语句。

SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
FROM table
WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
GROUP BY CAST(date AS DATE)
HAVING SUM(amount) = (SELECT MAX(total_amount)
                      FROM (SELECT CAST(date AS DATE), SUM(amount) AS total_amount 
                            FROM table
                            WHERE date BETWEEN '2019-01-01 00:00:00' AND '2019-12-31 00:00:00'
                            GROUP BY CAST(date AS DATE)
                           ) t
                     );

推荐阅读