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

[ShawyerPeng]

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

感谢各位大佬~

[ccgoing10]

最后面那个 sql 在 group by 的时候把 SubOrderID 去掉试试