同一语句中的group by和order by(每月)

ygya80vv 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(481)

是否可以在同一语句中分组依据和排序依据？下面的语句以随机顺序输出月份。它产生了正确的数字等，但我想显示月份为1月，2月，3月，4月等，按顺序

SELECT coalesce(date_format(TRANSACTION_DATE, '%M'), 'Grand Total') AS MONTH,
ROUND(SUM(SALES_AMOUNT),2) TOTAL, SUM(QUANTITY) AS TOTAL_QUANTITY
FROM SALES
WHERE YEAR(TRANSACTION_DATE) = 2018
GROUP BY month WITH ROLLUP;

April       30826.68    476
August      39703.85    619
December    36986.16    515
February    34531.15    463
January     45163.22    596
July        37640.08    506
June        37284.25    496
March       44194.98    621
May         39016.04    576
November    39161.90    521
October     34447.61    485
September   34958.94    498
            453914.86   6372

mysql group-by sql-order-by

来源：https://stackoverflow.com/questions/49949810/group-by-order-by-in-the-same-statement-per-month

2条答案

按热度按时间

cqoc49vn1#

应按预期顺序排列月份：

SELECT coalesce(date_format(TRANSACTION_DATE, '%M'), 'Grand Total') AS MONTH,
ROUND(SUM(SALES_AMOUNT),2) TOTAL, SUM(QUANTITY) AS TOTAL_QUANTITY
FROM SALES
WHERE YEAR(TRANSACTION_DATE) = 2018
GROUP BY month WITH ROLLUP
ORDER BY date_format(TRANSACTION_DATE, '%m');

注意小写字母 m 按顺序排列。这是月份的数字表示。

赞(0）回复(0）举报 2021-06-21

piah890a2#

是否可以在同一语句中分组依据和排序依据？是的，这是可能的，只是你需要解决它。
如果可以从此查询中除去汇总，则可以按如下方式进行排序：

SELECT coalesce(date_format(TRANSACTION_DATE, '%M'), 'Grand Total') AS MONTH, ROUND(SUM(SALES_AMOUNT),2) TOTAL, SUM(QUANTITY) AS TOTAL_QUANTITY FROM SALES WHERE YEAR(TRANSACTION_DATE) = 2018 GROUP BY month order by month(TRANSACTION_DATE) asc;

赞(0）回复(0）举报 2021-06-21

我来回答

同一语句中的group by和order by(每月)

2条答案

相关问题

热门标签

最新问答