问按日计算寄存器，包括零

EN

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
  on dbo.Dates([Date]);

declare @fromdate date = dateadd(day  , datediff(day  , 0, getdate() )-30 , 0); 
declare @thrudate date = dateadd(day  , datediff(day  , 0, getdate() ), 0);
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

select 
    d.Date
  , count(t.DownloadDate) as DownloadCount
from dates d
  left join TBL_Downloads t
    on d.date = convert(date,t.DownloadDate)
   and t.userid = 292
where d.date >= dateadd(day  , datediff(day  , 0, getdate() )-30 , 0)
  and d.date <= dateadd(day  , datediff(day  , 0, getdate() ), 0)
group by d.date

+------------+---------------+
|    Date    | DownloadCount |
+------------+---------------+
| 2017-02-27 |             0 |
| 2017-02-28 |             0 |
| 2017-03-01 |             2 |
| 2017-03-02 |             1 |
| 2017-03-03 |             0 |
| 2017-03-04 |             1 |
| 2017-03-05 |             1 |
| 2017-03-06 |             1 |
| 2017-03-07 |             0 |
| 2017-03-08 |             0 |
| 2017-03-09 |             0 |
| 2017-03-10 |             0 |
| 2017-03-11 |             0 |
| 2017-03-12 |             0 |
| 2017-03-13 |             0 |
| 2017-03-14 |             0 |
| 2017-03-15 |             0 |
| 2017-03-16 |             0 |
| 2017-03-17 |             0 |
| 2017-03-18 |             0 |
| 2017-03-19 |             0 |
| 2017-03-20 |             0 |
| 2017-03-21 |             0 |
| 2017-03-22 |             0 |
| 2017-03-23 |             0 |
| 2017-03-24 |             0 |
| 2017-03-25 |             0 |
| 2017-03-26 |             0 |
| 2017-03-27 |             0 |
| 2017-03-28 |             0 |
| 2017-03-29 |             0 |
+------------+---------------+