sql-server - 将时间表打卡和打卡查询更改为交叉表查询

Question

我正在为我制作的时钟网站创建交叉表查询。目前我只是将结果显示在一个表格中，每一天都是它自己的行。

这是数据的一个例子。

id    type  user    time
4941  1     user1   2017-10-03 11:24:00
4954  2     user1   2017-10-03 14:43:00
4955  1     user1   2017-10-03 14:43:00
4967  2     user1   2017-10-03 16:00:00

这是计算本周数据的查询。

SELECT user as UserName, CONVERT (varchar(10), time, 101) AS Date, 
SUM(
    CASE WHEN type = 1 
        THEN - 1 * (datepart(hh , [time]) + datepart(mi , [time]) / 60.0) 
    ELSE datepart(hh , [time]) + datepart(mi , [time]) / 60.0 END) AS Total 
    FROM table WHERE (username LIKE 'user1') 
    and datepart(wk, [time]) = datepart(wk, GETDATE())  
    GROUP BY user, CONVERT (varchar(10), time, 101) ORDER BY Date DESC

这就像我想要的那样显示数据。

UserName Date          Total
user1    08/21/2019    4.316667
user1    08/20/2019    9.366666
user1    08/19/2019    8.283333

但是现在我想获取该数据并将其放入交叉表查询中，这样我就可以在每一行显示周数，并在该行的列中显示每日数据。到目前为止，我已经尝试使用枢轴来完成此操作。

SELECT  Year, Month, Week,  Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday 
FROM (SELECT  DATENAME(WEEKDAY,  CONVERT (varchar(10), time, 101)) DAY, DATEPART(wk, CONVERT (varchar(10), _time, 101)) as Week
, DATEPART(M, CONVERT (varchar(10), time, 101)) as Month, DATEPART(YYYY, CONVERT (varchar(10), time, 101)) as Year
, SUM(
    CASE 
        WHEN type = 1 
        THEN - 1 * (datepart(hh , [time]) + datepart(mi , [time]) / 60.0) 
    ELSE datepart(hh , [time]) + datepart(mi , [time]) / 60.0 
    END) AS Total 

    FROM table WHERE (username LIKE 'user1') 

    GROUP BY  CONVERT(varchar(10), time, 101) ) p       

PIVOT (SUM(Total)
    FOR DAY IN (Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday) ) pvt

ORDER BY Year, Month, Week DESC 

我取得了一些成功。数据似乎正确显示。但是，当我尝试按多个变量排序时，显示顺序不正常。

Year    Month   Week    Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday
2017    10  40  NULL    4.600000    0.000000    NULL    NULL    NULL    NULL
2018    3   11  0.966667    NULL    NULL    NULL    NULL    NULL    5.150000
2019    7   31  NULL    NULL    8.116667    NULL    NULL    NULL    NULL
2019    8   34  8.283333    9.366666    -7.483333   NULL    NULL    NULL    NULL
2019    8   33  8.166667    8.266667    8.083333    8.616668    8.883334    NULL    NULL
2019    8   32  8.350000    8.333333    8.466666    7.883333    7.516666    NULL    NULL
2019    8   31  NULL    NULL    NULL    8.833334    8.000001    NULL    NULL

它应该按年、周和月排序。当我只做这一年时，它可以正常工作。但是如果我添加周和月，它会以错误的顺序混淆数据。我究竟做错了什么？它应该看起来像这样。

    Year    Week    Month   Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday
2019    34  8   8.283333    9.366666    -7.483333   NULL    NULL    NULL    NULL
2019    33  8   8.166667    8.266667    8.083333    8.616668    8.883334    NULL    NULL
2019    32  8   8.350000    8.333333    8.466666    7.883333    7.516666    NULL    NULL
2019    31  8   NULL    NULL    NULL    8.833334    8.000001    NULL    NULL
2019    31  7   NULL    NULL    8.116667    NULL    NULL    NULL    NULL
2018    11  3   0.966667    NULL    NULL    NULL    NULL    NULL    5.150000
2017    40  10  NULL    4.600000    0.000000    NULL    NULL    NULL    NULL

另外，如何将周值汇总到总计列？

标签： sql-server