在SELECT子句中引用子查询。

I have a SELECT query like this:

我有这样一个SELECT查询:

SELECT
    SubQuery.Year            AS Year,
    SubQuery.Customer        AS Customer,
    SUM(SubQuery.OrderCount) AS OrderCount
FROM
    {complexquery} AS SubQuery
GROUP BY
    SubQuery.Year, SubQuery.Customer

which gives results like this:

结果是这样的:

+--------+----------+------------+
| year   | customer | ordercount |
+--------+----------+------------+
| 2015   | John     | 10         |
+--------+----------+------------+
| 2015   | Amy      | 20         |
+--------+----------+------------+
| 2015   | Bob      | 10         |
+--------+----------+------------+
| 2014   | John     | 200        |
+--------+----------+------------+
| 2014   | Amy      | 200        |
+--------+----------+------------+
| 2014   | Bob      | 200        |
+--------+----------+------------+

And I would like to add the "ratio" column where customer's portion of the whole year ordercount would be displayed, like this:

我还想添加“比例”列，其中显示客户全年订单数量的部分，如下所示:

+--------+----------+------------+-------+
| year   | customer | ordercount | ratio |
+--------+----------+------------+-------+
| 2015   | John     | 10         | 0.25  |
+--------+----------+------------+-------+
| 2015   | Amy      | 20         | 0.5   |
+--------+----------+------------+-------+
| 2015   | Bob      | 10         | 0.25  |
+--------+----------+------------+-------+
| 2014   | John     | 200        | 0.33  |
+--------+----------+------------+-------+
| 2014   | Amy      | 200        | 0.33  |
+--------+----------+------------+-------+
| 2014   | Bob      | 200        | 0.33  |
+--------+----------+------------+-------+

I tried something like:

我试着喜欢的东西:

SELECT
    SubQuery.Year            AS Year,
    SubQuery.Customer        AS Customer,
    SUM(SubQuery.OrderCount) AS OrderCount
    SUM(SubQuery.OrderCount) / (SELECT SUM(OrderCount) FROM SubQuery WHERE...) AS Ratio
FROM
    {complexquery} AS SubQuery
GROUP BY
    SubQuery.Year, SubQuery.Customer

But was not successfull - I can not reference SubQuery in the SELECT clause.

但是没有成功-我不能在SELECT子查询中引用子查询。

I think there should be some elegant way to solve it but I do not see it.

我认为应该有一些优雅的方法来解决这个问题，但是我没有看到。

I do not want to repeat {complexquery} in the SELECT as the {complexquery} is long.

我不想在SELECT中重复{complexquery}，因为{complexquery}很长。

I can not modify database schema.

我不能修改数据库模式。

I am using MS SQL server.

我正在使用MS SQL server。

1 个解决方案

#1

You can place your select statement into a further subquery then use a window function to get the grand total and divide by this:

你可以将你的选择语句放入到一个进一步的子查询中，然后使用一个窗口函数来得到这个总数，然后除以这个:

WITH Subquery AS
(   SELECT
        SubQuery.[Year]          AS [Year],
        SubQuery.Customer        AS Customer,
        SUM(SubQuery.OrderCount) AS OrderCount
    FROM
        {complexquery} AS SubQuery
    GROUP BY
        SubQuery.[Year], SubQuery.Customer
)
SELECT  [Year],
        Customer,
        OrderCount,
        1.0 * OrderCount / SUM(OrderCount) OVER(PARTITION BY [Year]) AS Ratio
FROM    Subquery;

WORKING EXAMPLE

工作示例

DECLARE @ComplexSubquery TABLE ([year] INT, Customer VARCHAR(10), OrderCount INT);

INSERT @ComplexSubquery ([year], Customer, OrderCount)
VALUES
    (2015, 'John', 10),
    (2015, 'Amy', 20),
    (2015, 'Bob', 10),
    (2014, 'John', 200),
    (2014, 'Amy', 200),
    (2014, 'Bob', 200);

WITH Subquery AS
(   SELECT
        SubQuery.[Year]          AS [Year],
        SubQuery.Customer        AS Customer,
        SUM(SubQuery.OrderCount) AS OrderCount
    FROM
        @ComplexSubquery AS SubQuery
    GROUP BY
        SubQuery.[Year], SubQuery.Customer
)
SELECT  [Year],
        Customer,
        OrderCount,
        1.0 * OrderCount / SUM(OrderCount) OVER(PARTITION BY [Year]) AS Ratio
FROM    Subquery;

#1