对每行的日期范围进行分组

Question

我正在尝试输出每日、每周、每月和每年的订单数量以及这些时间范围内这些订单的总金额。目前我想出了以下内容。它很长，但它只是同一份声明的复制和粘贴工作，只是稍微修改为按年、月、周和日进行分组。

SELECT YEAR(s.LastUpdated) AS 'Year', 0 AS 'Month', 0 AS 'Week', 0 AS 'Day', count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE YEAR(s.LastUpdated) = 2011
GROUP BY
YEAR(s.LastUpdated)
    UNION
SELECT YEAR(s.LastUpdated) AS 'Year', MONTHNAME(s.LastUpdated) AS 'Month', 0 AS 'Week', 0 AS 'Day', count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE MONTH(s.LastUpdated) = 1
GROUP BY
YEAR(s.LastUpdated), MONTH(s.LastUpdated)
    UNION
SELECT YEAR(s.LastUpdated) AS 'Year', MONTHNAME(s.LastUpdated) AS 'Month', WEEKOFYEAR(s.LastUpdated) AS 'Week', 0 AS 'Day',  count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE WEEK(s.lastUpdated) = 4
GROUP BY
YEAR(s.LastUpdated), MONTH(s.LastUpdated), WEEK(s.LastUpdated)
    UNION
SELECT YEAR(s.LastUpdated) AS 'Year', MONTHNAME(s.LastUpdated) AS 'Month', WEEKOFYEAR(s.LastUpdated) AS 'Week', DAY(s.lastUpdated), count(rowId) AS 'Transactions', ROUND(SUM(Amount),2) AS 'Partner Cut'
FROM gfw_orders AS o
JOIN gfw_sage_orders AS s
ON o.sageRef = s.VendorTxCode
WHERE DAY(s.LastUpdated) = 26
GROUP BY
YEAR(s.LastUpdated), MONTH(s.LastUpdated), WEEK(s.LastUpdated), DAY(s.LastUpdated)

哪个输出

Year     Month     Week    Day   Count   Amount
2011  0             0      0      3      285.00
2011  January       0      0      3      285.00
2011  January       4      0      2      190.00
2011  January       4      26     1       95.00

哪个是理想的输出，提供可以相应输出的年、月、周、日计数和交易金额。但这仅适用于一种类型的订单。在订单表中，有两种类型的订单：在线订单和终端订单，理想情况下，我希望在同一查询中输出这两种类型，而不是多次查询数据库。

我有两个问题。首先，是否有更简单的方法可以达到相同的结果？我不想让它变得过于复杂，因为导致第二个问题，因为有些订单不同，如果我简单地复制和联合我已有的代码和添加的'，我希望结果显示在线订单和终端订单WHERE Terminal = 1' 由于并集，结果是相同的。有没有办法在同一个查询中输出两组输出？

我的目标可能过于复杂化，但提前感谢您的任何建议或指示。

Answer 1

你可以像这个例子一样尝试使用GROUP BY WITH ROLLUP

mysql> SELECT year, country, product, SUM(profit)
    -> FROM sales
    -> GROUP BY year, country, product WITH ROLLUP;

结果将是

+------+---------+------------+-------------+
| year | country | product    | SUM(profit) |
+------+---------+------------+-------------+
| 2000 | Finland | Computer   |        1500 |
| 2000 | Finland | Phone      |         100 |
| 2000 | Finland | NULL       |        1600 |
| 2000 | India   | Calculator |         150 |
| 2000 | India   | Computer   |        1200 |
| 2000 | India   | NULL       |        1350 |
| 2000 | USA     | Calculator |          75 |
| 2000 | USA     | Computer   |        1500 |
| 2000 | USA     | NULL       |        1575 |
| 2000 | NULL    | NULL       |        4525 |
| 2001 | Finland | Phone      |          10 |
| 2001 | Finland | NULL       |          10 |
| 2001 | USA     | Calculator |          50 |
| 2001 | USA     | Computer   |        2700 |
| 2001 | USA     | TV         |         250 |
| 2001 | USA     | NULL       |        3000 |
| 2001 | NULL    | NULL       |        3010 |
| NULL | NULL    | NULL       |        7535 |
+------+---------+------------+-------------+

For Rerence with ROLLUP

您的查询非常大，这就是为什么我选择再举一个例子！