如何通过派生列
问题描述:
组我有这样的SQL:如何通过派生列
Select Hours, LastName, FirstName, UUID,
Case
When DatePart(WeekDay, Date) = 1 Then
Date - 6
Else
Date - DatePart(Weekday, Date) + 2
End as [Week]
From Entry
Where Date between '06/30/2014' and '10/31/2014'
,我想要做什么是由[周]列,我使用Case语句创建的组这一点。这是可能的吗?如果是这样,我该怎么做呢?
谢谢!
答
我猜你正在努力寻找一个员工的小时max or sum
。所以这样的事情应该可以帮到你。请记住,没有聚合函数的列应该出现在group by中。
SELECT Max(Hours),-- sum(Hours)
LastName,
FirstName,
UUID,
CASE
WHEN Datepart(WeekDay, Date) = 1 THEN Date - 6
ELSE Date - Datepart(Weekday, Date) + 2
END AS [Week]
FROM Entry
WHERE Date BETWEEN '06/30/2014' AND '10/31/2014'
GROUP BY LastName,
FirstName,
UUID,
CASE
WHEN Datepart(WeekDay, Date) = 1 THEN Date - 6
ELSE Date - Datepart(Weekday, Date) + 2
END
+0
这个技巧!谢谢! – 2014-10-31 17:37:42
答
您不能在SQL Server中的列别名组 - 你需要一群以表达:
Select Hours, LastName, FirstName, UUID,
Case
When DatePart(WeekDay, Date) = 1 Then
Date - 6
Else
Date - DatePart(Weekday, Date) + 2
End as [Week]
From Entry
Where Date between '06/30/2014' and '10/31/2014'
GROUP BY
Case
When DatePart(WeekDay, Date) = 1 Then
Date - 6
Else
Date - DatePart(Weekday, Date) + 2
End
虽然你可以使用子查询使它有点清洁:
SELECT * FROM
(
Select Hours, LastName, FirstName, UUID,
Case
When DatePart(WeekDay, Date) = 1 Then
Date - 6
Else
Date - DatePart(Weekday, Date) + 2
End as [Week]
From Entry
Where Date between '06/30/2014' and '10/31/2014'
)
GROUP BY [Week]
但是在任何情况下,您都需要决定如何在组表达式中集合而不是的值。 Hours
有意义做一笔款项,但您如何汇总名称和ID?如果没有聚集是必要的只是让他们出来的结果:
SELECT Week, SUM(Hours) FROM
(
Select Hours, LastName, FirstName, UUID,
Case
When DatePart(WeekDay, Date) = 1 Then
Date - 6
Else
Date - DatePart(Weekday, Date) + 2
End as [Week]
From Entry
Where Date between '06/30/2014' and '10/31/2014'
)
GROUP BY [Week]
+0
如果我把非聚合的项目放在组中,sql server很开心。我会尝试你的解决方案。 – 2014-10-31 17:29:35
简单用了别名 – 2014-10-31 17:18:06
添加组相同的代码我做到这一点,得到了,因为它不是在聚合函数或包含在此“列Entry.Hours'在选择列表中无效GROUP BY子句“。 – 2014-10-31 17:20:36
如果不包含组中的其他列,则需要对它们进行汇总(即SUM,MAX,MIN等) – DavidG 2014-10-31 17:21:58