MYSQL查询一年中12个月的数据,补全12个月.

1.建立12个月的视图

CREATE VIEW 12_month_view AS

SELECT 01 AS month

UNION SELECT 02 AS month

UNION SELECT 03 AS month

UNION SELECT 04 AS month

UNION SELECT 05 AS month

UNION SELECT 06 AS month

UNION SELECT 07 AS month

UNION SELECT 08 AS month

UNION SELECT 09 AS month

UNION SELECT 10 AS month

UNION SELECT 11 AS month

UNION SELECT 12 AS month;

2.使用视图,左外链接 自己的查询语句 视图也相当于一张月份表.

SELECT
t.month as month ,
IFNULL(b.c,0) as count
FROM
12_month_view t
left join
(SELECT
DATE_FORMAT(business_deal_date,’%m’) as m,
COUNT(*) as c
FROM
j_op_member
where
is_new = 1
AND
DATE_FORMAT(business_deal_date,’%Y’) = ‘2020’
AND
product_type_no = 200
group BY
DATE_FORMAT(business_deal_date,’%Y-%m’)) AS b
ON
t.month = b.m ;