Group By SQL 语句 - 将结果合并到一行

Question

我创建了一个名为 tbl_timesheet_hours 的数据库表，如下所示：

timesheet_no
work_order_no
work_order_date
work_order_hours

表中填充的数据如下：

timesheet_no    work_order_no   work_order_date     work_order_hours
4               NTS0026B        2012-01-02          1
4               NTS0031B        2012-01-02          9
4               NTS0031B        2012-01-03          8
4               NTS0031B        2012-01-04          7
4               NTS0031B        2012-01-05          6
4               NTS0031B        2012-01-06          5
4               NTS0031B        2012-01-07          4
4               NTS0031B        2012-01-08          3

我需要创建一个查询，该查询将构成考勤表报告的基础，该考勤表报告可以以典型承包商的考勤表格式打印，即将在第一列中显示 work_order_no 字段，后面是每天分配的小时数。我创建的查询是：

SELECT
  work_order_no,
  switch(Format(work_order_date,'ddd') = "Mon", SUM(work_order_hours)) AS [Mon],
  switch(Format(work_order_date,'ddd') = "Tue", SUM(work_order_hours)) AS [Tue],
  switch(Format(work_order_date,'ddd') = "Wed", SUM(work_order_hours)) AS [Wed],
  switch(Format(work_order_date,'ddd') = "Thu", SUM(work_order_hours)) AS [Thu],
  switch(Format(work_order_date,'ddd') = "Fri", SUM(work_order_hours)) AS [Fri],
  switch(Format(work_order_date,'ddd') = "Sat", SUM(work_order_hours)) AS [Sat],
  switch(Format(work_order_date,'ddd') = "Sun", SUM(work_order_hours)) AS [Sun]
FROM tbl_timesheet_hours
WHERE timesheet_no=4
GROUP BY work_order_date, work_order_no;

查询产生以下结果集，

work_order_no   Mon Tue Wed Thu Fri Sat Sun
NTS0026B         1                      
NTS0031B         9                      
NTS0031B             8                  
NTS0031B                 7              
NTS0031B                     6          
NTS0031B                         5      
NTS0031B                             4  
NTS0031B                                 3

是否可以重构查询以产生以下结果集？

work_order_no   Mon Tue Wed Thu Fri Sat Sun
NTS0026B        1                       
NTS0031B        9   8   7   6   5   4   3

任何类型的帮助将不胜感激。谢谢。

Answer 1

您将使用交叉表查询，如下所示：

TRANSFORM Sum(tbl_timesheet_hours.[work_order_hours]) AS SumOfwork_order_hours
SELECT tbl_timesheet_hours.[work_order_no], Sum(tbl_timesheet_hours.[work_order_hours]) AS     [Total Of work_order_hours]
FROM tbl_timesheet_hours
GROUP BY tbl_timesheet_hours.[work_order_no]
ORDER BY Format([work_order_date],'ddd')
PIVOT Format([work_order_date],'ddd') In ("Mon","Tue","Wed","Thu","Fri","Sat","Sun");

Answer 2

您是否尝试过在 SUM() 内使用 SWITCH 表达式？像这样：

SELECT
  work_order_no,
  SUM(switch(Format(work_order_date,'ddd') = "Mon", work_order_hours)) AS [Mon],
  SUM(switch(Format(work_order_date,'ddd') = "Tue", work_order_hours)) AS [Tue],
  SUM(switch(Format(work_order_date,'ddd') = "Wed", work_order_hours)) AS [Wed],
  SUM(switch(Format(work_order_date,'ddd') = "Thu", work_order_hours)) AS [Thu],
  SUM(switch(Format(work_order_date,'ddd') = "Fri", work_order_hours)) AS [Fri],
  SUM(switch(Format(work_order_date,'ddd') = "Sat", work_order_hours)) AS [Sat],
  SUM(switch(Format(work_order_date,'ddd') = "Sun", work_order_hours)) AS [Sun]
FROM tbl_timesheet_hours
WHERE timesheet_no=4
GROUP BY work_order_no;

除此之外，还从 GROUP BY 中删除 work_order_date。