qq_35335792
柠檬也有梦
采纳率100%
2020-06-18 14:44

sqlserver 如何显示一周的列表

已采纳
  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),'一二三四五六日')

图片说明

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

2条回答

  • Julcia 向阳的花儿 10月前

    附上代码

    ------------创建星期表----------------------
    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]
    
    
    点赞 1 评论 复制链接分享
  • lhzlhk lhzlhk 10月前

    需要一个辅助表(tmp),里面是七条记录,周一到周日,然后把这个辅助表和你之前的查询做一个左/右外连接即可。
    select t1.dayonweek, coalesce(t1.rtcount, 0) rtcount
    from (你的原查询) t1
    right join tmp t2
    on t1.dayonweek = t2.dayonweek

    点赞 1 评论 复制链接分享