时间戳

首页 > 解决方案 > 将 UNION ALL 与 GROUP BY 结合

sql - 将 UNION ALL 与 GROUP BY 结合

问题描述

我真的可以对这个查询使用一点帮助。

我有一张Products桌子。

CREATE TABLE [dbo].[Products](
    [Id] [int] NOT NULL,
    [Title] [nvarchar](50) NOT NULL,
    -- Additional column omitted
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

还有一张Transactions桌子。

CREATE TABLE [dbo].[Transactions](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Quantity] [decimal](9, 3) NOT NULL,
    [TotalAmount] [bigint] NOT NULL,
    -- Additional columns omitted
    CONSTRAINT [PK_Transactions] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    ) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [dbo].[Transactions]  WITH CHECK ADD  CONSTRAINT [FK_Transactions_Products] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Products] ([Id])

还有一张CCTransactions桌子。

CREATE TABLE [dbo].[CCTransactions](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProductId] [int] NOT NULL,
    [TimeStamp] [datetime] NOT NULL,
    [Quantity] [decimal](9, 3) NOT NULL,
    [TotalAmount] [bigint] NOT NULL,
    -- Additional columns omitted
    CONSTRAINT [PK_CCTransactions] PRIMARY KEY CLUSTERED 
    (
        [Id] ASC
    )
) ON [PRIMARY]

ALTER TABLE [dbo].[CCTransactions]  WITH CHECK ADD  CONSTRAINT [FK_CCTransactions_Products] FOREIGN KEY([ProductId])
REFERENCES [dbo].[Products] ([Id])

我需要一个属于给定日期范围内UNION ALL的所有Transactions和的查询，然后将它们分组为, , , 。CCTransactionsProduct.IdProduct.TitleSUM(Quantity)SUM(TotalAmount)

以下是我到目前为止所拥有的。它不会编译，但我包括在内，因为人们通常想看看你尝试了什么。它告诉我：

消息 8120，级别 16，状态 1，第 5
行列“Products.Id”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。
消息 209，级别 16，状态 1，第 14 行
不明确的列名称“Id”。

我了解这些错误，但不确定修复它们的最佳方法。在我添加联合之前，查询似乎正在工作。

DECLARE @dtStart DATE = '2016-08-01';
DECLARE @dtEnd DATE = '2016-08-31';

SELECT p.Id, p.Title, SUM(t.Quantity), SUM(t.TotalAmount) AS Amount
FROM Transactions t
INNER JOIN Products p ON t.ProductId = p.Id
WHERE t.[TimeStamp] >= @dtStart AND CAST(t.[TimeStamp] AS DATE) <= @dtEnd
UNION ALL
SELECT p.Id, p.Title, SUM(t.Quantity), SUM(t.TotalAmount) AS Amount
FROM CCTransactions t
INNER JOIN Products p ON t.ProductId = p.Id
WHERE t.[TimeStamp] >= @dtStart AND CAST(t.[TimeStamp] AS DATE) <= @dtEnd
GROUP BY p.Id, p.Title
ORDER BY Title

标签： sqlsql-server

解决方案

你需要2个单独GROUP BY的s：

SELECT p.Id, p.Title, SUM(t.Quantity), SUM(t.TotalAmount) AS Amount
FROM Transactions t
INNER JOIN Products p ON t.ProductId = p.Id
WHERE t.[TimeStamp] >= @dtStart AND CAST(t.[TimeStamp] AS DATE) <= @dtEnd
GROUP BY p.Id, p.Title

UNION ALL

SELECT p.Id, p.Title, SUM(t.Quantity), SUM(t.TotalAmount) AS Amount
FROM CCTransactions t
INNER JOIN Products p ON t.ProductId = p.Id
WHERE t.[TimeStamp] >= @dtStart AND CAST(t.[TimeStamp] AS DATE) <= @dtEnd
GROUP BY p.Id, p.Title

或子查询：

SELECT      X.id, X.Title, SUM(X.Quantity), SUM(X.TotalAmount)
FROM        (
                SELECT p.Id, p.Title, t.Quantity, t.TotalAmount
                FROM Transactions t
                INNER JOIN Products p ON t.ProductId = p.Id
                WHERE t.[TimeStamp] >= @dtStart AND CAST(t.[TimeStamp] AS DATE) <= @dtEnd

                UNION ALL

                SELECT p.Id, p.Title, t.Quantity, t.TotalAmount
                FROM CCTransactions t
                INNER JOIN Products p ON t.ProductId = p.Id
                WHERE t.[TimeStamp] >= @dtStart AND CAST(t.[TimeStamp] AS DATE) <= @dtEnd
            ) X
GROUP BY    X.Id, X.Title

或者您可以重写整个查询：

;WITH
    AllTransaction AS
    (
        SELECT      ProductID, Timestamp, Quantity, TotalAmount
        FROM        Transaction
        UNION ALL
        SELECT      ProductID, Timestamp, Quantity, TotalAmount
        FROM        CCTransaction
    )

SELECT      p.Id, p.Ttitle, SUM(t.Quantity), SUM(t.TotalAmount)
FROM        AllTransaction      t
INNER JOIN  Products            p   ON t.ProductId = p.Id
WHERE       @dtStart <= t.[Timestamp] AND t.[TimeStamp] < DATEADD(DAY, 1, @dtEnd)
GROUP BY    p.Id, p.Title

注意 CAST(t.[TimeStamp] AS DATE) <= @dtEnd：这种情况不是SARGable，这意味着索引和统计数据没有帮助。CAST(t.[TimeStamp] AS DATE)在您将函数应用于每一行之前，结果是未知的。这会导致表扫描（即读取整个表），而您可能只需要几行。如果Transactions和CCTransactions是巨大的，你会受苦。

推荐阅读