weixin_42853935
2019-04-12 11:28
采纳率: 100%
浏览 396

sql server 的语句统计外包人员出勤时间

图片说明

需要sql语句将这张表
name 姓名 dept部门组织 date 考勤日期 timeTotal 工时总计
这四个栏位的数据组成如下图的查询结果。
需求:
每个月1-31天所有的数据对应栏位显示,最上面那个2019-04的抬头和每个人的最后total工时总计可以不用计算。只要帮我拼出sql语句就好了,应该是个交叉报表,可能高手还要指导我一下怎么虚拟出一个月31个日的列名,请高手提供完整的思路最好是代码,感谢

图片说明

可以用临时表的存储过程来做,也可以新增一张实体表,抬头是月的每日固定列,请高手多多指点

  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • o(╯□╰)o ! 2019-04-12 11:58
    已采纳

    图片说明

    SELECT
        t.dept,
        t.name,
        MAX ( CASE WHEN RIGHT(t.[date], 2) = '01' THEN t.timeTotal END ) '1',
        MAX ( CASE WHEN RIGHT(t.[date], 2) = '02' THEN t.timeTotal END ) '2',
        MAX ( CASE WHEN RIGHT(t.[date], 2) = '03' THEN t.timeTotal END ) '3' 
    FROM
        Test t 
    GROUP BY
        t.dept, t.name
    

    RIGHT(t.[date], 2) // 从右往左截取2个字符串

    已采纳该答案
    打赏 评论
  • l_w610 2019-04-12 12:31
    SELECT 
         dept,
         name,
         SUM(CASE WHEN DAY(date)=1 THEN timeTotal ELSE 0 END) 一号,
         SUM(CASE WHEN DAY(date)=2 THEN timeTotal ELSE 0 END) 二号,
         SUM(CASE WHEN DAY(date)=3 THEN timeTotal ELSE 0 END) 三号,
         SUM(CASE WHEN DAY(date)=4 THEN timeTotal ELSE 0 END) 四号,
         SUM(CASE WHEN DAY(date)=5 THEN timeTotal ELSE 0 END) 五号,
         SUM(CASE WHEN DAY(date)=6 THEN timeTotal ELSE 0 END) 六号,
         SUM(CASE WHEN DAY(date)=7 THEN timeTotal ELSE 0 END) 七号,
         SUM(CASE WHEN DAY(date)=8 THEN timeTotal ELSE 0 END) 八号,
         SUM(CASE WHEN DAY(date)=9 THEN timeTotal ELSE 0 END) 九号,
         SUM(CASE WHEN DAY(date)=10 THEN timeTotal ELSE 0 END) 十号,
    
         SUM(CASE WHEN DAY(date)=11 THEN timeTotal ELSE 0 END) 十一号,
         SUM(CASE WHEN DAY(date)=12 THEN timeTotal ELSE 0 END) 十二号,
         SUM(CASE WHEN DAY(date)=13 THEN timeTotal ELSE 0 END) 十三号,
         SUM(CASE WHEN DAY(date)=14 THEN timeTotal ELSE 0 END) 十四号,
         SUM(CASE WHEN DAY(date)=15 THEN timeTotal ELSE 0 END) 十五号,
         SUM(CASE WHEN DAY(date)=16 THEN timeTotal ELSE 0 END) 十六号,
         SUM(CASE WHEN DAY(date)=17 THEN timeTotal ELSE 0 END) 十七号,
         SUM(CASE WHEN DAY(date)=18 THEN timeTotal ELSE 0 END) 十八号,
         SUM(CASE WHEN DAY(date)=19 THEN timeTotal ELSE 0 END) 十九号,
         SUM(CASE WHEN DAY(date)=20 THEN timeTotal ELSE 0 END) 二十号,
    
         SUM(CASE WHEN DAY(date)=21 THEN timeTotal ELSE 0 END) 二一号,
         SUM(CASE WHEN DAY(date)=22 THEN timeTotal ELSE 0 END) 二二号,
         SUM(CASE WHEN DAY(date)=23 THEN timeTotal ELSE 0 END) 二三号,
         SUM(CASE WHEN DAY(date)=24 THEN timeTotal ELSE 0 END) 二四号,
         SUM(CASE WHEN DAY(date)=25 THEN timeTotal ELSE 0 END) 二五号,
         SUM(CASE WHEN DAY(date)=26 THEN timeTotal ELSE 0 END) 二六号,
         SUM(CASE WHEN DAY(date)=27 THEN timeTotal ELSE 0 END) 二七号,
         SUM(CASE WHEN DAY(date)=28 THEN timeTotal ELSE 0 END) 二八号,
         SUM(CASE WHEN DAY(date)=29 THEN timeTotal ELSE 0 END) 二九号,
         SUM(CASE WHEN DAY(date)=30 THEN timeTotal ELSE 0 END) 三十号,
         SUM(CASE WHEN DAY(date)=31 THEN timeTotal ELSE 0 END) 三十一号,
         SUM(timeTotal) total
    FROM table_name
    GROUP BY dept,name,LEFT(date,7)
    
    打赏 评论

相关推荐 更多相似问题