sql - 避免对特定列进行 GROUP BY

Question

我正在考虑避免在大型查询中按特定列分组以减少性能问题。

示例:http://sqlfiddle.com/#!18/cb98e/2

CREATE TABLE [OrderTable] 
(
    [id] INT,
    [OrderGroupID] INT,
    [Total] INT,
    [fkPerson] INT,
    [fkitem] INT,

    PRIMARY KEY (id)
) 

INSERT INTO [OrderTable] (id, OrderGroupID, Total, [fkPerson], [fkItem]) 
VALUES ('1', '1', '20', '1', '1'),
       ('2', '1', '45', '2', '2'),
       ('3', '2', '32', '1', '1'),
       ('4', '2', '30', '2', '2');

CREATE TABLE [Person] 
(
    [id] INT, 
    [Name] VARCHAR(32),
    PRIMARY KEY (id)
) 

INSERT INTO [Person] (id, Name) 
VALUES ('1', 'Fred'),
       ('2', 'Sam');

CREATE TABLE [Item] 
(
    [id] INT,
    [ItemNo] VARCHAR(32),
    [Price] INT,
    PRIMARY KEY (id)
) 

INSERT INTO [Item] (id, ItemNo, Price) 
VALUES ('1', '453', '23'),
       ('2', '657', '34');

查询:

WITH TABLE1 AS 
(
    SELECT 
        P.ID AS [PersonID], 
        P.Name, 
        SUM(OT.[Total]) AS [Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson
    FROM 
        OrderTable OT
    INNER JOIN 
        Person P ON P.ID = OT.fkperson
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        P.ID, P.Name, i.id, ot.fkperson
)
SELECT 
    *,
    Totalrows = (SELECT MAX(rownum) FROM TABLE1)
FROM 
    TABLE1

结果:

| PersonID | Name | Total | ItemID | rownum | fkperson | Totalrows |
+----------+------+-------+--------+--------+----------+-----------+
|        1 | Fred |    52 |      1 |      1 |        1 |         2 |
|        2 |  Sam |    75 |      2 |      2 |        2 |         2 |

现在，例如，如果我不想按 varchar 列(人名)进行 GROUP BY，我可以这样做 - 删除 person 表并稍后重新加入。例如

WITH TABLE1 AS 
(
    SELECT 
        -- P.ID AS [PersonID], 
        -- P.Name, 
        SUM(OT.[Total]) AS [Total], 
        i.[id] AS [ItemID],
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rownum,
        ot.fkperson
    FROM 
        OrderTable OT
    -- INNER JOIN Person P ON P.ID = OT.fkperson
    INNER JOIN 
        Item I ON I.[id] = OT.[fkItem]
    GROUP BY 
        -- P.ID, P.Name,
        i.id, ot.fkperson
)
SELECT 
    p.id as [PersonID],
    p.Name,
    t1.[total],
    t1.[itemid],
    t1.[rownum],
    t1.fkperson
    -- Totalrows = (SELECT MAX(rownum) FROM TABLE1 GROUP BY
    --    i.id
    --  ,ot.fkperson        
          --   )
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson

结果:

| PersonID | Name | total | itemid | rownum | fkperson |
+----------+------+-------+--------+--------+----------+
|        1 | Fred |    52 |      1 |      1 |        1 |
|        2 |  Sam |    75 |      2 |      2 |        2 |

我的问题是我还想包含 MAX(rownum) 列，但我如何才能在上次查询中执行此操作而不必再次对所有内容进行分组？最好的方法是什么？我错过了一些非常明显的事情吗？ :)

Answer 1

您也可以使用CROSS APPLY。并计算您可以使用的总数 COUNT(*) OVER()

SELECT 
    P.id as [PersonID],
    P.Name,
    T1.[total],
    I.id as [itemid],
    ROW_NUMBER() OVER (
            ORDER BY (SELECT NULL)
            ) AS [rownum],
    T1.fkperson,
    COUNT(*) OVER () Totalrows 
FROM 
    Item I
    CROSS APPLY (SELECT ot.fkperson, SUM(OT.[Total]) AS [total] 
                 FROM OrderTable OT 
                 WHERE I.[id] = OT.[fkItem] 
                 GROUP BY ot.fkperson ) AS T1
    INNER JOIN Person P ON P.id = t1.fkperson

结果:

PersonID    Name        total       itemid      rownum    fkperson    Totalrows
----------- ----------- ----------- ----------- --------- ----------- -----------
1           Fred        52          1           1         1           2
2           Sam         75          2           2         2           2