欢迎来到星创云技术支持社区，马上注册，享更多服务。 公众号：bjxcyun
您需要 登录 才可以下载或查看，没有账号？立即注册
x
E3常用SQL语句查询
一、缺货SQL
（1）缺货订单列表
- SELECT
- order_id,
- sum(yfpqh) AS yfpqh
- FROM
- order_goods
- WHERE
- order_id IN(
- SELECT
- oi.order_id
- FROM
- order_info oi
- WHERE
- 1 = 1
- AND oi.shipping_status IN(1)
- AND oi.trans_time <UNIX_TIMESTAMP(now())
- ORDER BY oi.order_id DESC)
- GROUP BY order_id DESC;
（2）缺货订单列表-商品导出
- SELECT
- og.order_sn,og.deal_code,og.sku_id,sum(og.goods_number) as num,sum(og.yfpqh) as yfpqh
- FROM
- order_info oi
- LEFT JOIN order_goods og ON oi.order_id = og.order_id
- LEFT JOIN sku_ex ON og.sku_id = sku_ex.sku_id
- LEFT JOIN order_promotion op ON oi.order_id = op.order_id
- WHERE
- 1 = 1
- AND oi.shipping_status IN(1)
- AND oi.trans_time < UNIX_TIMESTAMP(now())
- GROUP BY og.order_sn,og.sku_id
- ORDER BY
- og.order_id DESC;
（3）缺货订单商品列表
导出缺货商品清单
- SELECT
- og.order_id, og.order_sn, og.sku_id, og.sku,
- sum(og.yfpqh) as yfpqh, sum(og.pfhqh) as pfhqh
- FROM order_info oi
- INNER JOIN order_goods og ON og.order_id = oi.order_id
- INNER JOIN kehu k ON oi.sd_id = k.id
- INNER JOIN goods gs ON gs.goods_id = og.goods_id
- WHERE oi.order_status != 3
- GROUP BY oi.order_id,og.sku_id HAVING MAX(yfpqh) > 0;
（4）按SKU汇总导出
- SELECT
- og.order_id, og.order_sn, og.sku_id, og.sku,
- sum(og.yfpqh) AS yfpqh,
- sum(og.pfhqh) AS pfhqh
- FROM order_info oi
- INNER JOIN order_goods og ON og.order_id = oi.order_id
- INNER JOIN kehu k ON oi.sd_id = k.id
- LEFT JOIN cangku ck ON oi.fhck_id = ck.id
- LEFT JOIN color cr ON og.color_id = cr.color_id
- LEFT JOIN size se ON og.size_id = se.size_id
- WHERE
- oi.order_status != 3
- GROUP BY
- oi.order_id,
- oi.fhck_id,
- og.sku_id
- HAVING
- MAX(yfpqh)> 0;
|
|
bjtom