代码之家 › 专栏 › 技术社区 › Ryan Gadsdon

在临时表-SQL Server中完成计算

aggregate sql-server sql

Ryan Gadsdon · 技术社区 · 6 年前

桌子:

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 
    t1.fkperson,
    t1.[itemid],
    t1.[total],
    t1.[rownum]
    -- Totalrows = (SELECT MAX(rownum) FROM TABLE1)
FROM 
    TABLE1 T1
INNER JOIN 
    Person P ON P.ID = T1.fkperson

我试图完成临时表中某列的sum函数,并将其连接回CTE。要么出错,要么我得到了不正确的列。这样做的目的是在临时表中执行计算,以提高查询的性能。我怎样才能加入 sum() 从临时表到原始表的d列并输出结果?

当前查询:

CREATE TABLE #ot
(
     fkperson int, 
     Total int
)

INSERT INTO #ot
    SELECT 
        fkperson, 
        SUM(total) AS [Total]
    FROM 
        OrderTable
    GROUP BY 
        [fkperson]

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

2 回复 | 直到 5 年前

Gordon Linoff 6 年前

如果我理解正确(根据评论),你会:

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,
             COUNT(*) OVER () as cnt
             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
     )

我认为临时表没有任何用处,除非您希望在查询之间持久化结果。

TylerH Ash Burlaczenko 5 年前

我可能错了,但我认为您试图在#ot表中建立总行数,然后将其属性分配给最后一个select语句中输出的每一行。所以每一行都有表中最大的行数。

如果这是对的,那么我认为我对您的原始代码所做的以下更改可以做到这一点:

CREATE TABLE #ot
(
 fkperson int, 
 [fkItem] int,  --LS23 added to make code run
 Total int
)

INSERT INTO #ot
SELECT 
    fkperson, 
    [fkItem], --LS23 added to make code run
    SUM(total) AS [Total]
FROM 
    [#OrderTable]
GROUP BY 
    [fkperson],
    [fkitem]; --LS23 added to make code run

Totalrows = (SELECT MAX(rownum) FROM TABLE1)

这有帮助吗?