sql-查询不同状态下的数据之和

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;

 

转载于:https://www.cnblogs.com/xlsxls/p/9224788.html