sql union all与group by

kulphzqa 于 2021-07-26 发布在 Java

关注(0)|答案(1)|浏览(482)

我试图得到一份员工休假时间列表，并在网格中显示两年的数据。我试过这个方法，但不包括查询和联合，还有下面的代码，似乎无法使它工作。有什么想法吗？
表中的数据如下所示：

ID  IDEmployee IDJob IDTask StartDate Day1 Day2 Day3 Day4 Day5 Day6 Day7  
2472    5   1072    41  2019-01-07  5   4   1   1   2.5 0   0  
2474    5   1072    21  2019-01-07  1   1   2   1.5 1   0   0  
2477    5   1025    21  2019-01-07  2   3   5   2   3   0   0  
2484    9   1049    3   2019-01-07  1   0.5 0   0   0   0   0  
2485    9   1068    41  2019-01-07  6   6.5 7   4.5 8.5 0   0

我想要的输出是员工每年在某项任务中的总时间，即休假、工程时间等：
姓名total2020 total2019
杰克48 60
我正在使用sql server

Select Name, TotalHours from (
Select tblEmployee.Firstname + ' ' +  tblEmployee.LastName as Name, sum(day1) + sum(Day2) + sum(day3) + sum(day4) + sum(day5) + sum(day6) + sum(day7) as 'TotalHours' 
from tblTimeSheet 
inner join tblEmployee on tblTimeSheet.IDEmployee = tblEmployee.ID 
where StartDate > '1/1/2020' and idtask in (29,31,32,33) 
group by tblEmployee.Firstname, tblEmployee.Lastname 
--order by Firstname

union all

Select tblEmployee.Firstname + ' ' +  tblEmployee.LastName as Name, sum(day1) + sum(Day2) + sum(day3) + sum(day4) + sum(day5) + sum(day6) + sum(day7) as 'TotalHours' 
from tblTimeSheet 
inner join tblEmployee on tblTimeSheet.IDEmployee = tblEmployee.ID 
where StartDate > '1/1/2019' and StartDate < '1/1/2020' and idtask in (29,31,32,33) 
group by tblEmployee.Firstname, tblEmployee.Lastname 
as x group by Name

sql union sql-server group-by

来源：https://stackoverflow.com/questions/62219971/sql-union-all-with-group-by

1条答案

按热度按时间

rm5edbpk1#

我想你只需要条件聚合：

Select e.Firstname + ' ' +  e.LastName as Name,
       sum(case when startdate >= '2020-01-01' and 
                then day1 + day2 + day3 + day4 + day5 + day6 + day7
           end) as TotalHours_2020,
       sum(case when startdate >= '2019-01-01' and startdate < '2020-01-01'
                then day1 + day2 + day3 + day4 + day5 + day6 + day7
           end) as TotalHours_2019
from tblTimeSheet ts join
     tblEmployee e
     on t.IDEmployee = e.ID 
where idtask in (29, 31, 32, 33) and
      StartDate >= '2019-01-01'
group by e.Firstname, e.Lastname

赞(0）回复(0）举报 2021-07-26

我来回答

sql union all与group by

1条答案

相关问题

热门标签

最新问答