sqlserver 如何显示一周的列表

Question

1. 当前只能获取有数据的日期
2. 比如我想展示信息是： 周一 0 周二 0 周三 0 周四 0 周五 0 周六 0 周日 0

没数据也要显示

SET DATEFIRST 1--设置星期一为每周的开始
select 
    case 
    when tt.dayOnweek='星期天' then '周日'
    else replace(tt.dayOnweek,'星期','周')
    end as dayOnweek,ISNULL(tt.RYCount,0) as RYCount from 
(
    select
 dayOnweek,
    count(RYCount) as RYCount from(
        select
        MineName,
        datename(Weekday,DownWellTime) as dayOnweek,
        count(WorkNumber) as RYCount
        from [GL_RYDW_MonthCheckRecord202006]
        where DATEDIFF(WK,DATEADD(DD,-@@DATEFIRST,GETDATE()),DATEADD(DD,-@@DATEFIRST,DownWellTime))=0
        group by DownWellTime,WorkNumber,MineName
    )as t
group by t.dayOnweek,t.RYCount
)as tt
order by charindex(substring(tt.dayOnweek,3,1),'一二三四五六日')

Answer 1

附上代码

------------创建星期表----------------------
IF OBJECT_ID (N'dbo.dayseven', N'U') IS NOT NULL
DROP TABLE dbo.dayseven;
create table dbo.dayseven(dayname varchar(10))
insert into dbo.dayseven 
values('周一'),('周二'),('周三'),('周四'),('周五'),('周六'),('周日');
------------------------------------------
SET DATEFIRST 1--设置星期一为每周的开始
select s.[dayname] as dayOnweek,ISNULL(RYCount,0) from 
(select 
    case 
    when tt.dayOnweek='星期天' then '周日'
    else replace(tt.dayOnweek,'星期','周')
    end as dayOnweek,ISNULL(tt.RYCount,0) as RYCount from 
(
    select
 dayOnweek,
    count(RYCount) as RYCount from(
        select
        MineName,
        datename(Weekday,DownWellTime) as dayOnweek,
        count(WorkNumber) as RYCount
        from [GL_RYDW_MonthCheckRecord202006]
        where DATEDIFF(WK,DATEADD(DD,-@@DATEFIRST,GETDATE()),DATEADD(DD,-@@DATEFIRST,DownWellTime))=0
        group by DownWellTime,WorkNumber,MineName
    )as t
group by t.dayOnweek,t.RYCount
)as tt
order by charindex(substring(tt.dayOnweek,3,1),'一二三四五六日')
)  as a
right join dbo.dayseven as s on a.dayOnweek=s.[dayname]