如何从select语句中创建的列进行分组？

How can I group by a derived column in the select statement?

如何在select语句中按派生列进行分组?

I have a query:

我有一个问题:

select sum(hours) as summedhours, vendoremployeeid, vendorUserName, lineofbusiness, timesheetdate, 
case 
when datepart(weekday,timesheetdate) =1 then dateadd(weekday,4,timesheetdate)
when datepart(weekday,timesheetdate) =2 then dateadd(weekday,3,timesheetdate)
when datepart(weekday,timesheetdate) =3 then dateadd(weekday,2,timesheetdate)
when datepart(weekday,timesheetdate) =4 then dateadd(weekday,1,timesheetdate)
when datepart(weekday,timesheetdate) =5 then dateadd(weekday,0,timesheetdate)
when datepart(weekday,timesheetdate) =6 then dateadd(weekday,6,timesheetdate)
when datepart(weekday,timesheetdate) =7 then dateadd(weekday,5,timesheetdate)
end 
as FridaysGrouping
from viewProjects
group by FridaysGrouping, vendoremployeeid, vendorusername, lineofbusiness, timesheetdate
order by FridaysGrouping asc

When I run the above, it gives me an error that states column name "FridaysGrouping' doesn't exist. I believe this is because the order of execution for an sql server is : from > where > group by > having > select > order by

当我运行上面的命令时,它给出了一个错误,指出列名“FridaysGrouping”不存在。我相信这是因为sql server的执行顺序是:from> where> group by> having> select> order通过

What is the best way to group by the derived column above?

按上面派生列分组的最佳方法是什么?

2 个解决方案

#1

Keep the same computed column in group by

保持组中的相同计算列

select sum(hours) as summedhours, vendoremployeeid, vendorUserName, lineofbusiness, timesheetdate, 
case 
when datepart(weekday,timesheetdate) =1 then dateadd(weekday,4,timesheetdate)
when datepart(weekday,timesheetdate) =2 then dateadd(weekday,3,timesheetdate)
when datepart(weekday,timesheetdate) =3 then dateadd(weekday,2,timesheetdate)
when datepart(weekday,timesheetdate) =4 then dateadd(weekday,1,timesheetdate)
when datepart(weekday,timesheetdate) =5 then dateadd(weekday,0,timesheetdate)
when datepart(weekday,timesheetdate) =6 then dateadd(weekday,6,timesheetdate)
when datepart(weekday,timesheetdate) =7 then dateadd(weekday,5,timesheetdate)
end 
as FridaysGrouping
from viewProjects
group by FridaysGrouping, vendoremployeeid, vendorusername, lineofbusiness, timesheetdate,
case 
when datepart(weekday,timesheetdate) =1 then dateadd(weekday,4,timesheetdate)
when datepart(weekday,timesheetdate) =2 then dateadd(weekday,3,timesheetdate)
when datepart(weekday,timesheetdate) =3 then dateadd(weekday,2,timesheetdate)
when datepart(weekday,timesheetdate) =4 then dateadd(weekday,1,timesheetdate)
when datepart(weekday,timesheetdate) =5 then dateadd(weekday,0,timesheetdate)
when datepart(weekday,timesheetdate) =6 then dateadd(weekday,6,timesheetdate)
when datepart(weekday,timesheetdate) =7 then dateadd(weekday,5,timesheetdate)
end
order by FridaysGrouping asc

or use CTE. Find the computed column in CTE then do the group by in outer select. which is more readable

或使用CTE。在CTE中找到计算列,然后在外部选择中执行group by。哪个更具可读性

with cte as
(
select hours , vendoremployeeid, vendorUserName, lineofbusiness, timesheetdate, 
case 
when datepart(weekday,timesheetdate) =1 then dateadd(weekday,4,timesheetdate)
when datepart(weekday,timesheetdate) =2 then dateadd(weekday,3,timesheetdate)
when datepart(weekday,timesheetdate) =3 then dateadd(weekday,2,timesheetdate)
when datepart(weekday,timesheetdate) =4 then dateadd(weekday,1,timesheetdate)
when datepart(weekday,timesheetdate) =5 then dateadd(weekday,0,timesheetdate)
when datepart(weekday,timesheetdate) =6 then dateadd(weekday,6,timesheetdate)
when datepart(weekday,timesheetdate) =7 then dateadd(weekday,5,timesheetdate)
end 
as FridaysGrouping
from viewProjects
)
SELECT Sum(hours) summedhours,
       vendoremployeeid,
       vendorUserName,
       lineofbusiness,
       timesheetdate,
       FridaysGrouping
FROM   cte
GROUP  BY FridaysGrouping,
          vendoremployeeid,
          vendorusername,
          lineofbusiness,
          timesheetdate
ORDER  BY FridaysGrouping ASC

#2

-2

I had this problem few days ago and I solved it by using Inner Join.

几天前我遇到了这个问题,我使用Inner Join解决了这个问题。

Select ... , col1, ... From ...
Inner Join
(Select ... as col1, ... From ...  --newly created column here can be used outside as col1 directly.
)tb1
On ...
Where ...
Group by ...
Order by ...

#1