sql – 如何在没有执行任何活动时扩展查询以在单元格中添加0

我有以下查询,它可以很好地显示每天播放的板球时间.我只需要在没有板球比赛时显示0.目前正在跳过这些日期.有关更多参考,请参阅此 link.

;WITH CTE AS (
SELECT email,last_update,activity,starttime,endtime,duration as [Totaltime] from users 
WHERE activity='cricket' and email='abc'
GROUP BY email,duration,last_update
)
Select activity,cast(starttime as date) as date,SUM(datediff(second,endtime))/60.0 as TimePerDay
from cte
where starttime >= dateadd(day,-15,last_update)
group by activity,cast(starttime as date)

查询的当前输出是

activity | date        | TimePerDay
cricket  | 2015-06-16  | 80.01
cricket  | 2015-06-17  | 90.01
cricket  | 2015-06-18  | 120.01
cricket  | 2015-06-22  | 10.01

所需的输出是

activity | date        | TimePerDay
cricket  | 2015-06-16  | 80.01
cricket  | 2015-06-17  | 90.01
cricket  | 2015-06-18  | 120.01
cricket  | 2015-06-19  | 0
cricket  | 2015-06-20  | 0
cricket  | 2015-06-21  | 0
cricket  | 2015-06-22  | 10.01

解决方法

Set Nocount On;

Declare  @MinDate   Date,@MaxDate   Date

Declare @test Table
(
     activity       Varchar(100),date           Date,TimePerDay     Decimal(5,2)
)

Declare @result Table
(
     activity       Varchar(100),2) Default 0
)

;WITH CTE AS 
(
    SELECT   email,duration As Totaltime 
    From    users With (Nolock)
    WHERE   activity ='cricket' 
            And email = 'abc'
    GROUP BY email,last_update
)

Insert Into @test(activity,date,TimePerDay)
Select   activity,Cast(starttime as date) As date,endtime))/60.0 As TimePerDay
From    cte With (Nolock)
where   starttime >= dateadd(day,cast(starttime as date)

Select   @MinDate = Min(Date),@MaxDate = Max(Date)
From    @test

;With AllDates As
(
    Select   @MinDate As xDate
    From    @test As t1
    Where   t1.date = @MinDate

    Union All

    Select  Dateadd(Day,1,xDate)
    From    AllDates As ad
    Where   ad.xDate < @MaxDate
)

一种方法是： – (左连接)

Select  'cricket' As activity,ad.xDate,Isnull(t.TimePerDay,0) As TimePerDay
From    AllDates As ad With (Nolock)
        Left Join @test As t On ad.xDate = t.date

另一种方式是： – (插入所有日期和更新)

Insert Into @result(activity,date)
Select  'cricket',ad.xDate
From    AllDates As ad With (Nolock)

Update  t
Set     t.TimePerDay = t1.TimePerDay
From    @result As t
        Join @test As t1 On t.date = t1.date

Select  *
From    @result As r

产量

更新

Declare  @MinDate   Date,@MaxDate   Date

Select   @MaxDate = Getdate(),@MinDate = Dateadd(Day,-14,@MaxDate)

;With AllDates As
(
    Select   @MinDate As xDate

    Union All

    Select  Dateadd(Day,xDate)
    From    AllDates As ad
    Where   ad.xDate < @MaxDate
)

Select   @activity As activity                      ---- @activity (your stored procedure parameter),0) As TimePerDay
From    AllDates As ad With (Nolock)
        Left Join @test As t On ad.xDate = t.date

（编辑：92站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!