查询根据机器号分组查出上个月跟上上个月的总交易额度，并计算流失率

原创

小楚留香 2024-05-18 17:09:07 ©著作权

文章标签 统计 文章分类 MySQL 数据库

©著作权归作者所有：来自51CTO博客作者小楚留香的原创作品，请联系作者获取转载授权，否则将追究法律责任

查询根据机器号 分组查出上个月跟上上个月的总交易额度，并计算流失率

SELECT
yonghuming,tongjiriqi,
    sn,
    SUM( jine ) AS total_jine
FROM
    h_erp_transaction_data 
WHERE
     (`pinpai` = '立刷' and `tongjiriqi` BETWEEN '2024-04-01' AND '2024-04-30' ) or (`pinpai` = '立刷' and `tongjiriqi` BETWEEN '2024-03-01' AND '2024-03-30' )
GROUP BY sn  ORDER BY yonghuming desc



SELECT
yonghuming,tongjiriqi,
    sn,
  SUM(
        CASE 
            WHEN MONTH(tongjiriqi) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH) THEN jine
            ELSE 0 
        END
    ) AS '上月总金额',
    SUM(
        CASE 
            WHEN MONTH(tongjiriqi) = MONTH(CURRENT_DATE - INTERVAL 2 MONTH) THEN jine
            ELSE 0 
        END
    ) AS '两个月前总金额'
FROM
    h_erp_transaction_data 
WHERE
     (`pinpai` = '立刷' and `tongjiriqi` BETWEEN '2024-04-01' AND '2024-04-30' ) or (`pinpai` = '立刷' and `tongjiriqi` BETWEEN '2024-03-01' AND '2024-03-30' )
GROUP BY sn  ORDER BY yonghuming desc


SELECT CURDATE(),CURRENT_DATE(),CURRENT_DATE,CURRENT_DATE - INTERVAL 1 MONTH, MONTH(CURRENT_DATE - INTERVAL 2 MONTH);

SELECT
yonghuming,tongjiriqi,
    sn,
  SUM(
        CASE 
            WHEN MONTH(tongjiriqi) = 4 THEN jine
            ELSE 0 
        END
    ) AS '上月总金额',
    SUM(
        CASE 
            WHEN MONTH(tongjiriqi) = 3 THEN jine
            ELSE 0 
        END
    ) AS '两个月前总金额'
FROM
    h_erp_transaction_data 
WHERE
     (`pinpai` = '立刷' and `tongjiriqi` BETWEEN '2024-04-01' AND '2024-04-30' ) or (`pinpai` = '立刷' and `tongjiriqi` BETWEEN '2024-03-01' AND '2024-03-30' )
GROUP BY sn  ORDER BY yonghuming desc