V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
HexHub
HexHub,一站式SSH、Docker、数据库连接管理工具,支持多种主流数据库、多窗口分屏、智能SQL编辑、极速数据处理、批量命令、云端同步,支持SSH跳板机、命令广播、历史命令、SFTP多端文件互传。
Promoted by xiwh
ShawyerPeng
V2EX  ›  数据库

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

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

    有两个表 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   ·   实用小工具   ·   2758 人在线   最高记录 6679   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 32ms · UTC 02:51 · PVG 10:51 · LAX 19:51 · JFK 22:51
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.