有两个表 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;
感谢各位大佬~
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.