子查询和基本查询 sql server 中的分组依据
我想知道以下是否可行。
我有一个表,我想检索 3 列数据:
日、工作时数总和、工作时数总和以及条件
我的查询是
SELECT Day, SUM(Regular + Extra + Overtime) AS [Potential Hours],
(SELECT SUM(Extra + Regular + Overtime) AS Expr1
FROM dbo.TICPlus_Effort_Billable_Only
WHERE (Manager NOT LIKE '%manager1%')) AS [Billed Hours]
FROM Billable AS Billable1
GROUP BY Day
通过此查询,我获得了子查询中每一行的所有数据的总和,但是我希望子查询包含按天分组的约束。是否可以在子查询中使用 group by 来执行此操作,以便我每天都能获得有条件的每日总和?
I want to know if the following is possible.
I have one table of which I want to retrieve 3 columns of data:
Day, Sum of hours worked, Sum of hours worked with condition
My query is
SELECT Day, SUM(Regular + Extra + Overtime) AS [Potential Hours],
(SELECT SUM(Extra + Regular + Overtime) AS Expr1
FROM dbo.TICPlus_Effort_Billable_Only
WHERE (Manager NOT LIKE '%manager1%')) AS [Billed Hours]
FROM Billable AS Billable1
GROUP BY Day
With this query I get the sum of all the data in the subquery for each row, but I would like to have the subquery that includes the constraint to be grouped by day. Is it possible to have a group by in the subquery to do this so I get the daily sum with condition on a daily basis?
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
发布评论
评论(3)
~没有更多了~
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
不,您将无法执行此操作,因为 SELECT 列表中的 SUB QUERY 将返回超过 1 个值。
您需要在 FROM 子句的 SUB 查询中执行此操作,
例如
No, you will not be able to do that as the SUB QUERY in the SELECT list will then return more that 1 Value.
You need to do it in a SUB Query in the FROM clause
Something like