例如:
-- 商品毛利率=(1-进价/平均单件售价)*100%
-- 进价in_price,平均单价售价price / cnt,
-- 涉及两表,tb_order_detail,tb_product_info
-- 店铺毛利率=(1-总进价成本/总销售收入)*100%
-- 总进价成本 in_price * cnt,总销售收入 price * cnt
-- 涉及两表,tb_order_detail,tb_product_info
WITH a AS
(
SELECT pi.product_id,ov.order_id,in_price,price,cnt
FROM tb_product_info pi INNER JOIN
tb_order_detail od ON
pi.product_id = od.product_id
INNER JOIN tb_order_overall ov ON
ov.order_id = od.order_id
WHERE date(event_time) >= "2021-10-01"
AND shop_id = 901
)
SELECT "店铺汇总" as product_id,
concat ( format( (( 1 - sum(in_price*cnt) / sum(price * cnt) )*100) , 1 ), "%")
FROM a
UNION ALL
SELECT product_id,
concat(format((1 - ( max(in_price) / (sum(price*cnt) / sum(cnt)) ) )*100,1),"%")
FROM a
WHERE product_id in
(
SELECT product_id
FROM a
GROUP BY product_id
HAVING 1 - (max(in_price) / (sum(price*cnt) / sum(cnt))) > 0.249
)
GROUP BY product_id
中的with a as的意思就是先执行
(
SELECT
pi.product_id,ov.order_id,in_price,price,cnt
FROM
tb_product_info pi
INNER
JOIN
tb_order_detail od
ON
pi.product_id = od.product_id
INNER
JOIN
tb_order_overall ov
ON
ov.order_id = od.order_id
WHERE
date
(event_time) >=
"2021-10-01"
AND
shop_id = 901
)
之后把结果放到一个临时表A中,再需要用的时候结合inner join,left join,union等进行表的查询。
临时表中多数用于存放子查询或者嵌套查询,这样可以大大的提升sql查询的速度。