选择按月.年分组的两个日期之间的用户计数,并为计数=0 的月份包括零。
当前查询 :
select count(u_id) as users, monthname(register_date) as month_name,year(register_date) as year
from user
where register_date >= '2017-01-01' and register_date <= '2018-01-31'
group by year(register_date), month(register_date);
返回结果:
users, month_name, year
'1', 'August', '2017'
预期输出:
users, month_name, year
0 'Jan' '2017'
0 'Feb' '2017'
0 'March' '2017'
0 'April' '2017'
0 'May' '2017'
0 'June' '2017'
0 'July' '2017'
1 'Aug' '2017'
0 'Sept' '2017'
0 'Oct' '2017'
0 'Nov' '2017'
0 'Dec' '2017'
0 'Jan' '2018'
我搜索了很多链接,但它不符合我对项目的要求。请帮忙。 提前感谢。
创建一个包含所有日期的表格,然后left join
您已有的日期。
select count(u.u_id) as users, monthname(z.Date) as
month_name,year(z.Date) as year
from
(
select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) Month as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
) as z
left join user as u on z.Date = u.register_date
where z.Date >= '2017-01-01' and z.Date <= '2018-01-31'
group by year(z.Date), month(z.Date);
创建日期,如下所示
请尝试此查询:
DECLARE @FromDate DATETIME, @ToDate DATETIME;
SET @FromDate = '2013-01-01';
SET @ToDate = '2014-12-31';
SELECT TOP (DATEDIFF(MONTH, @FromDate, @ToDate)+1)
MONTH(DATEADD(MONTH, number, @FromDate)) TheMonth,
YEAR(DATEADD(MONTH, number, @FromDate)) TheYear
INTO Data
FROM [master].dbo.spt_values
WHERE [type] = N'P' ORDER BY number;
select * from Data
select Data.TheMonth,Data.TheYear, Isnull(tmp.users,0) as usercount
from Data
LEFT OUTER JOIN (select count(1) as users, month(register_date) as month_name,year(register_date) as year
from user
where CONVERT(nvarchar(121), register_date , 121) >=CONVERT(nvarchar(121),@FromDate , 121)
AND CONVERT(nvarchar(121), register_date , 121) <=CONVERT(nvarchar(121), @ToDate , 121)
group by year(register_date), month(register_date) ) tmp on tmp.month_name = Data.TheMonth AND tmp.year = Data.TheYear
DROP TABLE Data
请创建一个包含您所有日期的临时表(#temp_tbl
(,然后以下查询应该会产生您期望的结果:
select count(u_id) as users,
monthname(register_date) as month_name,
year(register_date) as year
from [user]
where register_date >='2017-01-01' and register_date<='2018-01-31'
group by year(register_date),month(register_date)
union
select '0' as users,
monthname(register_date) as month_name,
year(register_date) as year
from #temp_tbl
where register_date >= '2017-01-01' and register_date<='2018-01-31'
问候 阿卜杜拉