主页 / coding / 问题 / 78980217
Accepted
iBeMeltin
Asked: 2024-09-13 08:07:19 +0800 CST

使用两个具有相同结构的表执行聚合

  • 772

我有两个表包含相同类型的数据，一个表包含当前日期，另一个表包含前一天数据：

当前的：

CREATE TABLE Current (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO Current (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12345, 10.50),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23456, 20.75),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34567, 30.80),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45678, 40.25),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56789, 50.60),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67890, 60.10),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78901, 70.95),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89012, 15.35);

以前的：

CREATE TABLE Previous (
    Col1 VARCHAR(50),
    Col2 VARCHAR(10), 
    Col3 VARCHAR(2), 
    Col4 DATE,       
    Col5 INT,         
    Col6 NUMERIC(5,2)
);

INSERT INTO Previous (Col1, Col2, Col3, Col4, Col5, Col6)
VALUES 
('ItemA', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-07-01', 12350, 10.55),
('ItemA', CASE WHEN LEFT('oH', 1) = UPPER(LEFT('oH', 1)) THEN 'Sell' ELSE 'Buy' END, 'oH', '2025-08-01', 23461, 20.80),
('ItemB', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-07-01', 34572, 30.85),
('ItemC', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-09-01', 45683, 40.30),
('ItemC', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 56794, 50.65),
('ItemD', CASE WHEN LEFT('Br', 1) = UPPER(LEFT('Br', 1)) THEN 'Sell' ELSE 'Buy' END, 'Br', '2025-09-01', 67905, 60.15),
('ItemE', CASE WHEN LEFT('rB', 1) = UPPER(LEFT('rB', 1)) THEN 'Sell' ELSE 'Buy' END, 'rB', '2025-07-01', 78916, 70.90),
('ItemE', CASE WHEN LEFT('Ho', 1) = UPPER(LEFT('Ho', 1)) THEN 'Sell' ELSE 'Buy' END, 'Ho', '2025-08-01', 89027, 15.40);

我使用这两个表运行此查询：

SELECT Col1, SUM(Col5) AS 'sum', 'C' AS 'Flag'
FROM Current
GROUP BY Col1
UNION
SELECT Col1, SUM(Col5) AS 'sum', 'P' AS 'Flag'
FROM Previous
GROUP BY Col1
ORDER BY Col1;

返回：

Col1    Sum    Flag
ItemA   35801   C
ItemA   35811   P
ItemB   34567   C
ItemB   34572   P
ItemC   102477  P
ItemC   102467  C
ItemD   67905   P
ItemD   67890   C
ItemE   167913  C
ItemE   167943  P

为了清晰起见，我添加了标志列，以便您可以知道值来自哪里。我需要编辑此查询以返回如下表：

Col1    Current Sum Previous Sum    Difference
ItemA   35811.          35801           10
ItemB   34572           34567            5
ItemC   102477          102467          10
ItemD   67905           67890           15
ItemE   167943          167913          30

但由于表是相同的，我无法弄清楚如何选择要聚合的正确值。

编辑：我应该注意到，表格显示当天数据和前一天数据，因此任一表格都可能包含不匹配的值。例如，一行数据可以“过期”，并且仅包含在前一个表格中，而不包含在当前表格中。一行也可以“添加”，并且仅包含在当前表格中，而不包含在前一个表格中。在这些情况下，需要将一个值填充为 0

sql

2 个回答

  1. Best Answer

    当您选择它们​​时，将它们选择到您想要的输出列中，例如Current查询进入Current Sum列，Previous查询进入Previous Sum列。

    WITH cte AS (
    SELECT Col1, Col5 [Current Sum], NULL [Previous Sum]
    FROM [Current]
    UNION ALL
    SELECT Col1, NULL, Col5
    FROM [Previous]
)
SELECT
    Col1
    , SUM([Current Sum]) [Current Sum]
    , SUM([Previous Sum]) [Previous Sum]
    , SUM([Previous Sum]) - SUM([Current Sum]) Difference
FROM cte
GROUP BY Col1
ORDER BY Col1;

    按要求退货：

    列1 当前总和 上一頁 不同之处
    项目A 35801 35811 10
    项目B 34567 34572 5
    项目C 102467 102477 10
    项目D 67890 67905 15
    项目E 167913 167943 三十

    注意 1：不要用 来分隔列名，''因为它不是官方分隔符，在某些情况下会产生意想不到的后果。
    注意 2：你不需要，UNION ALL因为UNION它UNION会删除你肯定不想要的重复项。而且它运行速度会因此变慢。

    数据库Fiddle

    • 5

  2. 我只需分别执行聚合，然后执行完整的外连接：

    WITH p AS 
(
  SELECT i = Col1, p = SUM(Col5)
  FROM Previous GROUP BY Col1
),
c AS 
(
  SELECT i = Col1, c = SUM(Col5)
  FROM [Current] GROUP BY Col1
),
s AS
(
  SELECT Item       = COALESCE(p.i, c.i),
         [Current]  = COALESCE(c.c, 0), 
         Previous   = COALESCE(p.p, 0)
  FROM p FULL OUTER JOIN c
    ON c.i = p.i
)
SELECT *, Difference = Previous - [Current] FROM s;
    • 5

相关问题