我有一个按人分组的sql server结果。SUMS是他们的每日数字和每月数字。
Person Team Daily Figure Month To Date Tony Team 1 53 635 Dan Team 2 47 172 Tom Team 3 17 232 Simon Team 2 16 655 Ben Team 3 17 232 Fred Team 2 16 655
我如何仅按小组2将这些结果分组？例如，我仍然希望显示其他人的个人结果，但我只希望将第2小组归为一组。
结果示例
Person Team Daily Figure Month To Date Tony Team 1 53 635 Tom Team 3 17 232 Ben Team 3 17 232 Team 2 Team 2 79 1482
谢谢更新：
SELECT Person = case when Team = 'Team 2' then 'Team 2' else Person END, Team, SUM(ISNULL(Figure,0)) AS 'Daily Figure', SUM(Month To Date) AS 'Month To Date'
这是我的选择，它是错误的
您可以在和中使用一个case表达式：Person``select``group by
case
Person``select``group by
select case when Team = 'Team 2' then 'Team 2' else Person end as Person , Team , sum([Daily Figure]) as [Daily Figure] , sum([Month To Date]) as [Month To Date] from t group by case when Team = 'Team 2' then 'Team 2' else Person end , Team
extrester演示：http：//rextester.com/XHQ24032
返回：
+--------+--------+--------------+---------------+ | Person | Team | Daily Figure | Month To Date | +--------+--------+--------------+---------------+ | Tony | Team 1 | 53 | 635 | | Team 2 | Team 2 | 79 | 1482 | | Ben | Team 3 | 17 | 232 | | Tom | Team 3 | 17 | 232 | +--------+--------+--------------+---------------+
如果要使用 cross apply() 以避免重复case表达式：
cross apply()
select x.Person , t.Team , sum(t.[Daily Figure]) as [Daily Figure] , sum(t.[Month To Date]) as [Month To Date] from t cross apply ( select case when t.Team = 'Team 2' then 'Team 2' else t.Person end as Person ) as x group by x.Person , t.Team
或搭配 cross apply(values()) ：
cross apply(values())
select x.Person , t.Team , sum(t.[Daily Figure]) as [Daily Figure] , sum(t.[Month To Date]) as [Month To Date] from t cross apply (values (case when t.Team = 'Team 2' then 'Team 2' else t.Person end) ) as x (Person) group by x.Person , t.Team
