imp_yunwei/查询库存的sql.txt

SELECT 
    t1.pono AS 采购合同号,
    t2.sono AS 销售合同号,
    t1.foursname AS 我方,
    t2.ctname AS 客户,
    t1.stockname AS 仓库名称,
    t1.sdesc AS 中文品名,
    t1.arrDt AS 到港时间,
    pp.dPort AS 目的港,
    pp.chnShipName AS 船名,
    t1.ut AS 单位,
    t1.total_qty AS 库存量,
    ABS(nvl(t2.total_qty_out, 0)) AS 出库量,
    ABS(nvl(t3.total_qty_in, 0)) AS 入库量,
    t1.poamt AS 采购金额,
    t1.soamt AS 销售金额,
  
    pp.create_by_name AS 制单人代码
FROM (
    SELECT 
        IFNULL(b.pono, '') AS pono,
        a.foursname,
        b.ctname,
        b.stockname, 
        b.sdesc,
        b.ut, 
        b.fcat, 
        b.qty, 
        b.poamt,
        b.soamt,
        b.sono, 
        b.id,
        b.create_by,
        a.arrDt,
        -- 计算该合同的总数量
        SUM(nvl(b.fcat, 0) * nvl(b.qty, 0)) OVER (PARTITION BY IFNULL(b.pono, '')) AS total_qty,
        -- 为每个合同的行编号，取第一条
        ROW_NUMBER() OVER (PARTITION BY IFNULL(b.pono, '') ORDER BY b.id) AS rn
    FROM mdkcdtl b
    LEFT JOIN mdkcctrl a ON a.id = b.rid
    WHERE a.placed = '2' 
        AND IFNULL(a.del_flag, 0) <> 1
        AND IFNULL(b.del_flag, 0) <> 1
        AND a.fmodalid NOT IN ('922', '923')
) t1
LEFT JOIN (
    SELECT 
        b.ctname as ctname,
        b.sono as sono,
        IFNULL(b.pono, '') AS pono,
        -- 计算fcat=-1的总数量
        SUM(nvl(b.fcat, 0) * nvl(b.qty, 0)) AS total_qty_out,
        -- 统计fcat=-1的记录数
        COUNT(*) AS count_minus1
    FROM mdkcdtl b
    LEFT JOIN mdkcctrl a ON a.id = b.rid
    WHERE a.placed = '2' 
        AND b.fcat = '-1'
        AND IFNULL(a.del_flag, 0) <> 1
        AND IFNULL(b.del_flag, 0) <> 1
        AND a.fmodalid NOT IN ('922', '923')
    GROUP BY IFNULL(b.pono, ''),b.ctname,b.sono
) t2 ON t1.pono = t2.pono
LEFT JOIN (
    SELECT 
        IFNULL(b.pono, '') AS pono,
        -- 计算fcat=-1的总数量
        SUM(nvl(b.fcat, 0) * nvl(b.qty, 0)) AS total_qty_in,
        -- 统计fcat=-1的记录数
        COUNT(*) AS count_minus1
    FROM mdkcdtl b
    LEFT JOIN mdkcctrl a ON a.id = b.rid
    WHERE a.placed = '2' 
        AND b.fcat = '1'
        AND IFNULL(a.del_flag, 0) <> 1
        AND IFNULL(b.del_flag, 0) <> 1
        AND a.fmodalid NOT IN ('922', '923')
    GROUP BY IFNULL(b.pono, '')
) t3 ON t1.pono = t3.pono
LEFT JOIN (
    SELECT 
        a1.pono,
        a1.dPort,
        a1.chnShipName,
        a1.totalQty,
        a1.create_by_name
     --  a2.sdesc
    FROM purchaseOrder a1 
  --  LEFT JOIN pOrderDtl a2 ON a1.id = a2.rid
    WHERE IFNULL(a1.del_flag, 0) <> 1   
    --   AND IFNULL(a2.del_flag, 0) <> 1
    AND a1.placed = '2'
) pp
 ON t1.pono = pp.pono
WHERE t1.rn = 1  -- 只取每个合同的第一行
    AND t1.stockname <> '直运库'
ORDER BY t1.pono;