SELECT
bd.device_no AS 设备编号,
SUM(IFNULL(bu.amount,0)) as 总营收,
SUM(CASE WHEN bu.is_online = 0 THEN bu.amount ELSE 0 END) as 线上,
SUM(CASE WHEN bu.is_online = 1 THEN bu.amount ELSE 0 END) as 线下
FROM
b_device_tbl bd
LEFT JOIN b_userdevice_rec_tbl bu ON bd.id = bu.device_id AND bu.create_time BETWEEN '2018-06-11 00:00:00' AND '2018-06-24 23:59:59'
WHERE
bd.device_no IN (
100004610,
100005658,
100005623
)
GROUP BY
bd.device_no;
案例2：
SELECT
SUM( case when s.TEAMNUMBER = "实体专供" then s.QUANTITY else 0 end ) AS salesQuantity,
sum( case when s.TEAMNUMBER = "实体专供" then s.ACTUAL_UNIT_PRICE else 0 end ) AS salesAmount,
SUM( case when s.TEAMNUMBER is null then s.QUANTITY else 0 end ) AS disSalesQuantity,
sum( case when s.TEAMNUMBER is null then s.ACTUAL_UNIT_PRICE else 0 end ) AS disSalesAmount,
s.SHOP_CODE
FROM
t_order_goods s
WHERE
s.TRADE_FINISH_TIME BETWEEN "2021-04-01 "
AND "2021-04-02"
GROUP BY s.SHOP_CODE
本文链接：https://my.lmcjl.com/post/15127.html
4 评论