我正在与以下问题作斗争,任何帮助都将不胜感激! 我需要求一个变量的总和,但条件的GROUP by Dependents的级别(请参见3),这超出了我对SQL的了解.
因此,我的查询应该是:
- 取每个ID的金额总和并拆分(参见F.E.ID_1,100+50)
- 客户具有最近日期的值(在ID上,拆分级别)(请参阅F.E.ID_1、A和B->;A)
- 如果客户具有相同的日期值(在ID上,拆分级别),它应该保留这两个值,并且还应该在金额上对每个客户进行拆分(参见F.E.ID_3、30和10+10)
我的虚构数据集如下所示(重新创建的脚本见下图):
ID | SPLIT | CUST | DATE | AMOUNT |
---|---|---|---|---|
ID_1 | SPLIT_YES | A | 05/01/2024 | 100 |
ID_1 | SPLIT_NO | A | 04/01/2024 | 200 |
ID_1 | SPLIT_YES | B | 03/01/2024 | 50 |
ID_2 | SPLIT_YES | A | 05/01/2024 | 50 |
ID_2 | SPLIT_NO | A | 04/01/2024 | 300 |
ID_2 | SPLIT_NO | B | 03/01/2024 | 300 |
ID_3 | SPLIT_YES | B | 04/01/2024 | 90 |
ID_3 | SPLIT_NO | B | 04/01/2024 | 30 |
ID_3 | SPLIT_NO | A | 04/01/2024 | 10 |
ID_3 | SPLIT_NO | A | 03/01/2024 | 10 |
查询的最终结果应该是:
ID | SPLIT | CUST | DATE | AMOUNT |
---|---|---|---|---|
ID_1 | SPLIT_YES | A | 05/01/2024 | 150 |
ID_1 | SPLIT_NO | A | 04/01/2024 | 200 |
ID_2 | SPLIT_YES | A | 05/01/2024 | 50 |
ID_2 | SPLIT_NO | A | 04/01/2024 | 600 |
ID_3 | SPLIT_YES | B | 04/01/2024 | 90 |
ID_3 | SPLIT_NO | B | 04/01/2024 | 30 |
ID_3 | SPLIT_NO | A | 04/01/2024 | 20 |
谢谢大家!
我试着使用很多不同的With语句一步一步地做,但没有正确的结果.使用WITH语句,我能够解决步骤1和步骤2的组合,方法是首先对每个ID求和,然后拆分,然后基于最新的Cust值进行连接,但这种连接是我的问题,因为它使步骤3不可能实现.
WITH LatestDatePerID AS (
SELECT ID,
"SPLIT",
MAX(DATE_COLUMN) AS MAX_DATE
FROM your_table_name
GROUP BY ID, "SPLIT"
),
LatestCustPerID AS (
SELECT t.ID,
t.CUST,
t."SPLIT",
t.DATE_COLUMN,
t.AMOUNT
FROM your_table_name t
JOIN LatestDatePerID l ON t.ID = l.ID AND t.DATE_COLUMN = l.MAX_DATE and t."SPLIT" = l."SPLIT"
)
SELECT ID,
CUST,
"SPLIT",
DATE_COLUMN,
SUM(AMOUNT) AS AMOUNT
FROM LatestCustPerID
GROUP BY ID, "SPLIT", CUST, DATE_COLUMN
ORDER BY ID, DATE_COLUMN DESC;
因此,它并不是把其他被忽视的行相加. 我为这件事伤透了脑筋.
重新创建表的脚本:
CREATE TABLE Test_Table_MM (
ID VARCHAR2(10),
SPLIT VARCHAR2(10),
CUST VARCHAR2(10),
DATE_COLUMN DATE,
AMOUNT NUMBER
);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_1', 'SPLIT_YES', 'A', TO_DATE('05/01/2024', 'MM/DD/YYYY'), 100);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_1', 'SPLIT_NO', 'A', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 200);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_1', 'SPLIT_YES', 'B', TO_DATE('03/01/2024', 'MM/DD/YYYY'), 50);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_2', 'SPLIT_YES', 'A', TO_DATE('05/01/2024', 'MM/DD/YYYY'), 50);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_2', 'SPLIT_NO', 'A', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 300);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_2', 'SPLIT_NO', 'B', TO_DATE('03/01/2024', 'MM/DD/YYYY'), 300);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_YES', 'B', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 90);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_NO', 'B', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 30);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_NO', 'A', TO_DATE('04/01/2024', 'MM/DD/YYYY'), 10);
INSERT INTO Test_Table_MM (ID, SPLIT, CUST, DATE_COLUMN, AMOUNT) VALUES ('ID_3', 'SPLIT_NO', 'A', TO_DATE('03/01/2024', 'MM/DD/YYYY'), 10);