mysql：一条sql语句统计环比、同比

有一个业务需求 

统计各个景区的入园人数、销售额以及入园人数和销售额的同比

以月度为例子同比为例；环比类似

思路分析：表a首先统计出当月的入园人数、销售额，表b统计出同期的入园人数、销售额（在a的基础上减去一年，可），将两个表进行左连接。

select 
    CASE WHEN b.TOTAL_COUNT is NULL OR b.TOTAL_COUNT=0 THEN 0 
    ELSE ((a.TOTAL_COUNT - b.TOTAL_COUNT)/b.TOTAL_COUNT)*100 //计算入园人数同比 
    END link_ratio_count, 
    CASE WHEN b.total_money is NULL OR b.total_money=0 THEN 0 
    ELSE ((a.total_money - b.total_money)/b.total_money)*100 //计算销售额同比
    END link_ratio_money,
a.TOTAL_COUNT as total_count_this,b.TOTAL_COUNT as total_count_last,a.total_money as total_money_this,b.total_money as total_money_last,a.PROJECT_NAME,a.PROJECT_NO 
FROM 
    (SELECT SUM(TOTAL_COUNT) as TOTAL_COUNT,SUM(PAY_MONEY-RETURN_COUNT*o.SELLER_PRICE) as total_money,PROJECT_NAME,o.PROJECT_NO 
	    FROM S_ORDER_C o 
	    LEFT JOIN S_PRODUCT d ON o.PRODUCT_NO=d.PRODUCT_NO 
        where DateDiff(mm,o.CREATE_TIME,GETDATE()) <= 1// sql server的写法筛选近一月
	    GROUP BY PROJECT_NAME,o.PROJECT_NO) a //根据景区名称和景区编码分组 
LEFT JOIN 
    (SELECT SUM(TOTAL_COUNT) as TOTAL_COUNT,SUM(PAY_MONEY-RETURN_COUNT*o.SELLER_PRICE) as total_money,PROJECT_NAME 
        FROM S_ORDER_C o 
	    LEFT JOIN S_PRODUCT d ON o.PRODUCT_NO=d.PRODUCT_NO 
	    where  DateDiff(mm,o.CREATE_TIME,'2018-01-01') = 0 
	    GROUP BY PROJECT_NAME) b ON a.PROJECT_NAME = b.PROJECT_NAME

 

本文地址：https://blog.csdn.net/pengyangchao/article/details/85990711