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 ;