E3电商常用SQL语句查询
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;
页:
[1]