V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
ShawyerPeng
V2EX  ›  数据库

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

  •  
  •   ShawyerPeng · 2020-01-13 20:29:59 +08:00 · 1387 次点击
    这是一个创建于 1536 天前的主题,其中的信息可能已经有所发展或是发生改变。

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

    感谢各位大佬~

    1 条回复    2020-01-14 10:42:56 +08:00
    ccgoing10
        1
    ccgoing10  
       2020-01-14 10:42:56 +08:00
    最后面那个 sql 在 group by 的时候把 SubOrderID 去掉试试
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   2766 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 26ms · UTC 12:02 · PVG 20:02 · LAX 05:02 · JFK 08:02
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.