请教：从一句 sql 返回的 id 列表遍历查询另一 sql 语句

有两个表 payment 和 cost，payment 表有一个 type 字段用于某个业务场景区分求和时是加还是减，orderid = 123 的单条 sql 语句可以写成如下：

SELECT ( (SELECT sum(amount) FROM payment f WHERE orderid = 123 AND providerid = 456 AND amount <> 0 AND type = 2) - (SELECT sum(amount) FROM payment WHERE orderid = 123 AND providerid = 456 AND amount <> 0 AND type = 1) - (SELECT sum(cost) FROM cost WHERE suborderid in (SELECT suborderid FROM suborder WHERE orderid = 123)) ) 

请教一下是否有纯 SQL 语句方式实现从一个集合（通过语句SELECT DISTINCT orderid FROM payment得到所有的 orderid ）遍历查询出每个 orderid 对应的 sum 求和结果。

有尝试用过游标和自连接的方式，但结果好像不对。

DECLARE @OrderId BIGINT DECLARE My_Cursor CURSOR FOR (SELECT DISTINCT OrderID FROM payment) OPEN My_Cursor; FETCH NEXT FROM My_Cursor INTO @OrderId; WHILE @@FETCH_STATUS = 0 BEGIN SELECT ( (SELECT sum(amount) FROM payment f WHERE orderid = @OrderId AND providerid = 456 AND amount <> 0 AND type = 2) - (SELECT sum(amount) FROM payment WHERE orderid = @OrderId AND providerid = 456 AND amount <> 0 AND type = 1) - (SELECT sum(cost) FROM cost WHERE suborderid in (SELECT suborderid FROM suborder WHERE orderid = @OrderId)) FETCH NEXT FROM My_Cursor INTO @OrderId; END CLOSE My_Cursor; DEALLOCATE My_Cursor; GO; 

SELECT f.OrderID, p.suborderid, SUM(CASE WHEN f.type = 2 THEN f.amount END) - SUM(CASE WHEN f.type = 1 THEN f.amount END) - SUM(p.Cost) FROM payment f, cost p, suborder s WHERE f.OrderID = s.OrderID AND p.SubOrderID = s.SubOrderID AND f.ProviderID = 456 AND f.amount <> 0 group by f.OrderID, p.SubOrderID; 

感谢各位大佬~