sql - 显示日期甚至零值 SQL

Question

我有 SQL 查询：

SELECT Date, Hours, Counts FROM TRANSACTION_DATE

示例输出：

     Date        | Hours | Counts
   ----------------------------------
   01-Feb-2018   |   20  |   5 
   03-Feb-2018   |   25  |   3 
   04-Feb-2018   |   22  |   3 
   05-Feb-2018   |   21  |   2 
   07-Feb-2018   |   28  |   1 
   10-Feb-2018   |   23  |   1 

如果你能看到，有几天因为没有数据/空而丢失，但我希望显示丢失的日期并且值为零：

     Date        | Hours | Counts
   ----------------------------------
   01-Feb-2018   |   20  |   5 
   02-Feb-2018   |    0  |   0
   03-Feb-2018   |   25  |   3 
   04-Feb-2018   |   22  |   3 
   05-Feb-2018   |   21  |   2
   06-Feb-2018   |    0  |   0 
   07-Feb-2018   |   28  |   1 
   08-Feb-2018   |    0  |   0
   09-Feb-2018   |    0  |   0
   10-Feb-2018   |   23  |   1 

提前谢谢你。

标签： sqlsql-server-2008

Answer 1

您需要生成一系列日期。如果没有太多，递归 CTE 是一种简单的方法：

with dates as (
      select min(date) as dte, max(date) as last_date
      from transaction_date td
      union all
      select dateadd(day, 1, dte), last_date
      from dates
      where dte < last_date
     )
select d.date, coalesce(td.hours, 0) as hours, coalesce(td.count, 0) as count
from dates d left join 
     transaction_date td
     on d.dte = td.date;