时间戳

首页 > 解决方案 > 如果没有记录，如何显示月份，如果在 MySQL 上为空，如何将其强制为零

mysql - 如果没有记录，如何显示月份，如果在 MySQL 上为空，如何将其强制为零

问题描述

我有一个订单表，我需要按月获取订单记录。但我有条款，如果一个月内没有数据，它仍应显示数据但强制为零，如下所示：

在此处输入图像描述

我所做的是使用我的查询：

select sum(total) as total_orders, DATE_FORMAT(created_at, "%M") as date 
from orders 
where is_active = 1
AND tenant_id = 2
AND created_at like '%2021%'
group by DATE_FORMAT(created_at, "%m")

但结果仅获取现有数据：

在此处输入图像描述

这里的任何人都可以帮我创建确切的查询吗？

太感谢了

标签： mysqlsql

解决方案

每当您尝试使用表中不存在的值时，一种选择是使用引用；无论是来自表还是查询生成的值。

我猜测，就日期数据而言，无论是哪一年，表中的列都可能包含一年中所有 12 个月的完整列表created_at。orders

假设表格数据orders从 2019 年到当前日期。LEFT JOIN有了它，您可以简单地为一个操作创建一个 12 个月的参考表。所以：

SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at);

您可以将其附加到您的查询中，例如：

SELECT IFNULL(SUM(total),0) as total_orders, mnt
from (SELECT MONTHNAME(created_at) mnt FROM orders GROUP BY MONTHNAME(created_at)) mn 
LEFT JOIN orders o
ON mn.mnt=MONTHNAME(created_at)
AND is_active = 1
AND tenant_id = 2
AND created_at like '%2021%'
GROUP BY mnt;

除了添加 12 个月子查询和 aLEFT JOIN之外，您的原始查询还有 3 个其他更改：

  1. IFNULL()如果值不存在，则添加到SUM()操作中SELECT以返回。0
  2. 所有WHERE条件都已切换到，ON因为保留它WHERE将使LEFT JOIN成为常态JOIN。
  3. GROUP BY正在使用子查询生成的月份 ( mnt) 值。

考虑到表orders可能没有完整的 12 个月，您可以从查询中生成它。有很多方法可以做到这一点，但在这里我只展示UNION适用于大多数 MySQL 版本的方法。

SELECT MONTHNAME(CONCAT_WS('-',YEAR(NOW()),mnt,'01')) dt
FROM
(SELECT 1 AS mnt UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION 
SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION 
SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) mn

如果您使用的是支持SEQUENCE ENGINE的 MariaDB 版本，则上面的相同查询要短得多：

SELECT MONTHNAME(CONCAT_WS('-',YEAR(NOW()),mnt,'01'))
FROM (SELECT seq AS mnt FROM seq_1_to_12) mn

我在这个演示小提琴中使用 MariaDB 10.5，但是似乎月份名称排序是基于名称值而不是月份本身，所以它看起来没有排序。如果它在MySQL 8.0 fiddle中，它的顺序是正确的。

推荐阅读