将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和CCTransactions那年秋天在指定日期范围内，然后它们分组为Product.Id，Product.Title，SUM(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

去世

首先对2个表使用UNION ALL并将结果连接到Products：

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 (
  SELECT ProductId, [TimeStamp], Quantity, TotalAmount FROM Transactions
  UNION ALL
  SELECT ProductId, [TimeStamp], Quantity, TotalAmount 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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。