MySQL 选择 sum 与 group by 和空值 [英] MySQL select sum with group by and empty values

问题描述

假设我有一家从 10 点到 15 点营业的商店，销售额将保存在下表中并带有时间戳:

let's assume I have a store which is open from 10 till 15 and the sales will save in the following table with time stamp:

+----------+---------------------+---------------+
| store_id | sales_date          | sales_amount  |
+----------+---------------------+---------------+
|        1 | 2017-11-03 10:32:44 | 100.00        | 
|        1 | 2017-11-03 10:33:49 | 150.00        |
|        1 | 2017-11-03 11:08:24 | 120.00        |
|        1 | 2017-11-03 13:33:57 | 200.00        |
|        2 | 2017-11-03 12:37:34 | 100.00        |
|        2 | 2017-11-03 15:23:49 | 200.00        |
+----------+---------------------+---------------+

我想使用 chart.js 显示范围从 10 到 15 的图表.

I would like to display a chart using chart.js with the range from 10 til 15.

我尝试了以下 SQL:

I tried the following SQL:

SELECT store_id, HOUR(sales_date) as group_hour, sum(sales_amount) as sales_sum 
FROM sales 
GROUP BY store_id, group_hour

我的当前结果是

+----------+-------------+---------------+
| store_id | group_hour  | sales_sum     |
+----------+-------------+---------------+
|        1 | 10          | 250.00        | 
|        1 | 11          | 120.00        |
|        1 | 13          | 200.00        |
|        2 | 12          | 100.00        |
|        2 | 15          | 200.00        |
+----------+-------------+---------------+

我的预期结果是

+----------+-------------+---------------+
| store_id | group_hour  | sales_sum     |
+----------+-------------+---------------+
|        1 | 10          | 250.00        | 
|        1 | 11          | 120.00        |
|        1 | 12          | 0.00          |
|        1 | 13          | 200.00        |
|        1 | 14          | 0.00          |
|        1 | 15          | 0.00          |
|        2 | 10          | 0.00          |
|        2 | 11          | 0.00          |
|        2 | 12          | 100.00        |
|        2 | 13          | 0.00          |
|        2 | 14          | 0.00          |
|        2 | 15          | 200.00        |
+----------+-------------+---------------+

有机会用 MySQL 做到这一点吗?

Is there a chance to do this with MySQL?

推荐答案

你可以使用这个:

SELECT b.store_id, b.group_hour, IFNULL(sales_sum, 0) AS sales_sum
FROM 
(
 SELECT store_id, HOUR(sales_date) as group_hour, sum(sales_amount) as sales_sum 
 FROM sales 
 GROUP BY store_id, group_hour
) a
RIGHT OUTER JOIN 
(
  SELECT *
  FROM
  (
   SELECT DISTINCT store_id
   FROM sales
  ) all_stores
  CROSS JOIN
  (
   SELECT 10 as group_hour
   UNION ALL
   SELECT 11
   UNION ALL
   SELECT 12
   UNION ALL
   SELECT 13
   UNION ALL
   SELECT 14
   UNION ALL
   SELECT 15
  ) all_hours
) b
ON a.store_id = b.store_id AND a.group_hour = b.group_hour
ORDER BY 1, 2

我将 RIGHT OUTER JOIN 与包含所有商店和所有时间(从 10 到 15 点)的表一起使用.

I am using RIGHT OUTER JOIN with the table that contains all stores and all hours (from 10 to 15).

这篇关于MySQL 选择 sum 与 group by 和空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！