缺货订单查询

SELECT s.*, b.all_number AS 仓库可用库存
    , TIMESTAMPDIFF(DAY, s.confirm_time, NOW()) AS 预警时间

FROM   (
    SELECT t.id_onl, tg.status_label AS tg_status_label, t.confirm_time, t.org_name, tg.org_id
        , t.consignee_name, t.consignee_phone, tg.goods_barcode, tg.goods_id_ofl, tg.goods_name
        , COUNT(tg.goods_barcode) AS number
    FROM mabao51.trade_goods_onl_4_bi tg
        LEFT JOIN mabao51.trade_onl t ON tg.trade_onl_id = t.id
    WHERE tg.org_id != 4000
        AND t.`status` BETWEEN 2 AND 8
        AND tg.`status` BETWEEN 0 AND 0
        AND tg.goods_name NOT LIKE "%测试%"
    GROUP BY t.id_onl, tg.goods_barcode
    ) s
    LEFT JOIN (
        SELECT gb.bar_code AS bar_code, gb.`name` AS NAME, SUM(igs.number) AS all_number
        FROM arm_changsha.inv_goods_stock igs
            INNER JOIN arm_changsha.goods_base gb ON gb.id = igs.goods_id
        WHERE igs.del_flag = "0"
            AND gb.`name` NOT LIKE "测试%"
            AND igs.department_id IN (25192, 1000)
            AND gb.bar_code IN (
                SELECT gb.bar_code
                FROM arm_changsha.inv_goods_stock igs
                    INNER JOIN arm_changsha.goods_base gb ON gb.id = igs.goods_id
                WHERE igs.department_id = 25192
            )
        GROUP BY gb.bar_code
        ORDER BY igs.department_id DESC
       ) b
ON b.bar_code = s.goods_barcode
WHERE s.goods_barcode NOT IN (6944639802717, "6798532102036-1")
    AND s.goods_name NOT LIKE "欢乐51大放价，门店购物好时节"
ORDER BY s.confirm_time DESC
LIMIT 3000