有两个表 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; 

感谢各位大佬~