sql - 如何聚合sql(HANA)中不同行的值？

Question

我有一个像这样定义的发货表(该表存储在 HANA 数据库中，如果相关的话):

CREATE COLUMN TABLE SHIPMENTS (
    ShipmentID INT PRIMARY KEY,
    Received INT,
    Facility NVARCHAR(10),
    Item NVARCHAR(20)
);

此处，“已接收”列表示收到每批货件的时间点，“设施”是收到货件的地点，“元素”是货件的内容。

我已经用这样的数据填充了它:

INSERT INTO SHIPMENTS VALUES (1, 0, 'Factory', 'Production machine');
INSERT INTO SHIPMENTS VALUES (2, 0, 'Office', 'Printer');
INSERT INTO SHIPMENTS VALUES (3, 0, 'Factory', 'Coffee maker');
INSERT INTO SHIPMENTS VALUES (4, 1, 'Office', 'Coffee maker');
INSERT INTO SHIPMENTS VALUES (5, 1, 'Factory', 'Fax Machine');
INSERT INTO SHIPMENTS VALUES (6, 2, 'Office', 'Computers');
INSERT INTO SHIPMENTS VALUES (7, 2, 'Factory', 'Fridge');
INSERT INTO SHIPMENTS VALUES (8, 2, 'Factory', 'Freezer');
INSERT INTO SHIPMENTS VALUES (9, 2, 'Office', 'Fax Machine');

我想查询数据库以查找在每个时间点到目前为止已收到的项目。根据另一个线程的答案，我首先这样做:

SELECT Facility, Received, STRING_AGG (Item, ';') as Items
    FROM (
        SELECT * FROM SHIPMENTS 
        ORDER BY Facility, Received
    )
GROUP BY Facility, Received
ORDER BY Facility, Received;

结果

  | FACILITY | RECEIVED | ITEMS
---------------------------------------------------------
1 | Factory  | 0        | Production Machine;Coffee maker
2 | Factory  | 1        | Fax Machine
3 | Factory  | 2        | Fridge;Freezer
4 | Office   | 0        | Printer
5 | Office   | 1        | Coffee maker
6 | Office   | 2        | Computers;Fax Machine

但是，我想要这个

  | FACILITY | RECEIVED | ITEMS
---------------------------------------------------------
1 | Factory  | 0        | Production Machine;Coffee maker
2 | Factory  | 1        | Production Machine;Coffee maker;Fax Machine
3 | Factory  | 2        | Production Machine;Coffee maker;Fax Machine;Fridge;Freezer
4 | Office   | 0        | Printer
5 | Office   | 1        | Printer;Coffee maker
6 | Office   | 2        | Printer;Coffee maker;Computers;Fax Machine

即，每一行显示当时收到的内容以及已经收到的所有内容。有没有一种好的方法可以在 SQL 中做到这一点？

Answer 1

您可以尝试在 select 子句中使用相关查询来生成您想要的 csv 数据:

SELECT
    Facility,
    Received,
    (SELECT STRING_AGG (s2.Item, ';') FROM SHIPMENTS s2
     WHERE s2.Facility = s1.Facility AND s2.Received <= s1.Received
     GROUP BY s2.Facility) AS ITEMS
FROM SHIPMENTS s1
GROUP BY
    Facility,
    Received
ORDER BY
    Facility;