对数据表进行计数并求和，然后用默认值填充空数据

Question

我有一个只包含日期数据的表，我想计算数据在同一个月的次数，对于这个问题它已经完成了，现在我得到了一个新问题，即缺少月份，这是因为没有关于该月份的数据。现在我想添加默认值为 0 的空月份，即使该月份不在表中。任何人都可以根据我的查询帮助我吗？

这是我的数据

start_date  |end_date
------------------------
2018-10-01 |2018-10-02
2018-01-04 |2018-02-04
2018-08-01 |2018-10-01

这是我的查询

select month(month_table) as month_table
, sum(cstart) as cstart 
, sum(cend) as cend 
from 
(
(select `start_date` as month_table
, 1 as cstart
, 0 as cend 
from newdata
) 
union all 
( select `end_date`
, 0
, 1 
from newdata 
) 
) dd 
group 
by monthname(month_table)
, month(month_table) 
order 
by month(month_table)

结果是

month_table|cstart|cend
1      |  1   |  0
2      |  0   |  1
8      |  1   |  0
10     |  1   |  2

我想添加新查询，所以我的结果将是

month_table|cstart|cend
1      |  1   |  0
2      |  0   |  1
3      |  0   |  0
4      |  0   |  0
5      |  0   |  0
6      |  0   |  0
7      |  0   |  0
8      |  1   |  0
9      |  0   |  0
10     |  1   |  2
11     |  0   |  0
12     |  0   |  0

这是我的小提琴 http://sqlfiddle.com/#!9/c18b5f/3/0

Answer 1

你需要一个包含所有月份的表格。您可以使用UNION ALL创建一个带有子查询的即席。然后向左将计数子查询连接到该表。

SELECT m.month month_table,
coalesce(s.count, 0) cstart,
coalesce(e.count, 0) cend
FROM (SELECT 1 month
UNION ALL
SELECT 2 month
UNION ALL
SELECT 3 month
UNION ALL
SELECT 4 month
UNION ALL
SELECT 5 month
UNION ALL
SELECT 6 month
UNION ALL
SELECT 7 month
UNION ALL
SELECT 8 month
UNION ALL
SELECT 9 month
UNION ALL
SELECT 10 month
UNION ALL
SELECT 11 month
UNION ALL
SELECT 12 month) m
LEFT JOIN (SELECT month(n.start_date) month,
count(*) count
FROM newdata n
GROUP BY month(n.start_date)) s
ON s.month = m.month
LEFT JOIN (SELECT month(n.end_date) month,
count(*) count
FROM newdata n
GROUP BY month(n.end_date)) e
ON e.month = m.month
ORDER BY m.month;

Answer 2

这应该是这个问题的答案：

IF EXISTS(select * FROM sys.views where name = 'monthnotexict')
drop view monthnotexict
go
create view monthnotexict as
select 1 as month_table,0 as cstart ,0 as cend  from newdata union 
select 2 as month_table,0 as cstart ,0 as cend from newdata union 
select 3 as month_table,0 as cstart,0 as cend from newdata union 
select  4 as month_table,0 as cstart,0 as cend from newdata union 
select  5 as month_table,0 as cstart,0 as cend from newdata union 
select  6 as month_table,0 as cstart,0 as cend from newdata union 
select  7 as month_table,0 as cstart,0 as cend from newdata union 
select  8 as month_table,0 as cstart,0 as cend  from newdata union 
select  9 as month_table,0 as cstart,0 as cend from newdata union 
select  10 as month_table,0 as cstart,0 as cend from newdata union 
select  11 as month_table,0 as cstart,0 as cend from newdata union 
select  12 as month_table,0 as cstart,0 as cend from newdata

go
SELECT DATENAME(month, DATEADD(month,M.month_table-1, CAST('2008-01-01' AS datetime)))as month_table
, sum(cstart) as cstart 
, sum(cend) as cend 
from monthnotexict left join
(
(select 
, 1 as cstart
, 0 as cend 
from newdata
) 
union all 
( 
, 0
, 1 
from newdata 
) 
union all
(
select
month_table
, sum(cstart) as cstart 
, sum(cend) as cend  
from monthnotexict
group by month_table
)

) dd on dd.cend=M.cend or dd.cstart=M.cstart
group by M.month_table

作为您在评论中的请求，我更改了我的代码(现在它没有错误，月份编号更改为月份名称(
我希望它完成！