SQL每日一题(20210317)

题目

有如下一张表T0317

希望得到如下结果：

即

测试数据

CREATE TABLE T0317 
(
ID INT,
Type VARCHAR(20),
MasterID INT,
Amount INT
)

INSERT INTO T0317 VALUES (1,'直接合同',NULL,5000);
INSERT INTO T0317 VALUES (2,'补充合同',1,1000);
INSERT INTO T0317 VALUES (3,'补充合同',1,500);
INSERT INTO T0317 VALUES (4,'直接合同',NULL,6000);
INSERT INTO T0317 VALUES (5,'直接合同',NULL,4000);
INSERT INTO T0317 VALUES (6,'补充合同',5,1000);

参考答案

官方答案:

SELECT T1.ID
,T1.[Type]
,T1.Amount AS amount
,SUM(T2.Amount) AS amount2
FROM T0317 T1 LEFT JOIN
T0317 T2 ON T1.ID=T2.MasterID
WHERE T1.[Type]='直接合同'
GROUP BY T1.ID
,T1.[Type]
,T1.Amount
ORDER BY ID

考点: 聚合分组,一层关系相互关联查找技巧(T1.ID=T2.MasterID)

参考:SQL每日一题(20210317)