当一个桶中不存在数据时，如何在存储桶中选择SQL数据？

Question

我正在尝试为给定的数据集获取一组完整的存储桶，即使某些存储桶没有记录。

例如，我希望按一周中的一天显示总计，没有记录的天数为零。

SELECT
WEEKDAY(transaction_date) AS day_of_week,
SUM(sales) AS total_sales
FROM table1
GROUP BY day_of_week

如果我每天都有销售额，那么我的结果中将有7行，代表了第0-6天的总销售额。

如果我在第二天没有销售，第二天就没有结果了。

什么是第二天强制零值最有效的方法？

我应该加入临时表还是定义的桶数组？'0'，'1'，'2'，'3'，'4'，'5'，'6‘

或者，在我完成查询之后，在MySQL之外插入零更好吗？

我使用的是MySQL，但这是一个通用的SQL问题。

Answer 1

在MySQL中，您可以简单地使用从1到7的派生数字表，将其与表合并，然后聚合：

select d.day_of_week, sum(sales) AS total_sales
from (
    select 1 day_of_week union all select 2 union all select 3 union all select 4
    union all select 5 union all select 6 union all select 7
) d
left join table1 t1 on weekday(t1.transaction_date) = d.day_of_week
group by day_of_week

最近的版本有values(row...)语法，这缩短了查询的时间：

select d.day_of_week, sum(sales) AS total_sales
from (values row(1), row(2), row(3), row(4), row(5), row(6), row(7)) d(day_of_week)
left join table1 t1 on weekday(t1.transaction_date) = d.day_of_week
group by day_of_week

Answer 2

基本上，当该桶的数据实际上为空时，您希望答案为0，因此您需要最大值( null，0)。max函数本机不会以这种方式使用NULL，但是，您可以使用合并强制执行：

COALESCE(MAX(SUM(sales)),0)

正如这个答案所建议的

Answer 3

首先，您需要一个日历表；类似于这或这。或者动态创建日历子集。我不确定mySQL语法，但这里是Server中的样子。

DECLARE
    @FromDate DATE
  , @ToDate   DATE

-- set these variables to appropriate values
SET @FromDate = '2020-03-01';
SET @ToDate = '2020-03-31';

;WITH cteCalendar (MyDate) AS
(
    SELECT CONVERT(DATE, @FromDate) AS MyDate
    UNION ALL
    SELECT DATEADD(DAY, 1, MyDate)
    FROM   cteCalendar
    WHERE  DATEADD(DAY, 1, MyDate) <= @ToDate
)
SELECT WEEKDAY(cte.MyDate) AS day_of_week,
SUM(sales) AS total_sales
FROM cteCalendar cte
LEFT JOIN table1 t1 ON cte.MyDate = t1.transaction_date
GROUP BY day_of_week