子查询和基本查询 sql server 中的分组依据

发布于 10-09 13:48 字数 556 浏览 3 评论 0原文

我想知道以下是否可行。

我有一个表,我想检索 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 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

暗藏城府2024-10-16 13:48:24

不,您将无法执行此操作,因为 SELECT 列表中的 SUB QUERY 将返回超过 1 个值。

您需要在 FROM 子句的 SUB 查询中执行此操作,

例如

 SELECT Day, 
        SUM(Regular + Extra + Overtime) AS [Potential Hours],
        SubSum AS [Billed Hours]
FROM    Billable AS Billable1 LEFT JOIN
        (
            SELECT  Day,
                    SUM(Extra + Regular + Overtime) AS SubSum
            FROM    dbo.TICPlus_Effort_Billable_Only
            WHERE   (Manager NOT LIKE '%manager1%')
            GROUP BY Day
        ) s ON  Billable1.Day = s.Day
GROUP BY    Day

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

 SELECT Day, 
        SUM(Regular + Extra + Overtime) AS [Potential Hours],
        SubSum AS [Billed Hours]
FROM    Billable AS Billable1 LEFT JOIN
        (
            SELECT  Day,
                    SUM(Extra + Regular + Overtime) AS SubSum
            FROM    dbo.TICPlus_Effort_Billable_Only
            WHERE   (Manager NOT LIKE '%manager1%')
            GROUP BY Day
        ) s ON  Billable1.Day = s.Day
GROUP BY    Day
与君绝2024-10-16 13:48:24
SELECT
    DAY,
    SUM(Regular + Extra + Overtime) AS [ Potential Hours ],
    (
        SUM CASE WHEN Manager NOT LIKE '%manager1%' THEN(Extra + Regular + Overtime)
    END
) AS Expr1
FROM
    dbo.TICPlus_Effort_Billable_Only
GROUP BY
    DAY
SELECT
    DAY,
    SUM(Regular + Extra + Overtime) AS [ Potential Hours ],
    (
        SUM CASE WHEN Manager NOT LIKE '%manager1%' THEN(Extra + Regular + Overtime)
    END
) AS Expr1
FROM
    dbo.TICPlus_Effort_Billable_Only
GROUP BY
    DAY
掩饰不了的爱2024-10-16 13:48:24

是的,你可以
“子查询可以在允许表达式的任何地方使用” 正如文档所说

Yes, You Can
"A subquery can be used anywhere an expression is allowed" As the doc says

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文