sql统计近四个季度的总值

最新推荐文章于 2023-02-20 12:48:33 发布

程序员阿文

最新推荐文章于 2023-02-20 12:48:33 发布

阅读量1.7k

点赞数

分类专栏：我的随笔文章标签： mysql

本文链接：https://blog.csdn.net/awen6666/article/details/103615096

版权

我的随笔专栏收录该内容

35 篇文章 1 订阅

订阅专栏

sql统计近四个季度的总值

quarter(date)
获取date当前季度，now()获取当前日期
if(expr1,expr2,expr3)
如果expr1为true则expr2，否则expr3
DATE_SUB(date,INTERVAL expr unit)
获取date日期减去expr个unit，unit可以是year，day，month，quarter等值
case when 判断条件 than 语句1 else 语句2 end
当判断条件为true时执行语句1，为false时执行语句2

近四个季度依次分别为q4,q3,q2,q1;本年总值为amount1.
select a.emp_id,
    sum(case when a.year=IF(QUARTER(NOW())<=3,YEAR(NOW())-1,YEAR(NOW())) and quarter=QUARTER(DATE_SUB(NOW(),INTERVAL 3 QUARTER)) then a.amount else null end) as q1 ,
    sum(case when a.year=IF(QUARTER(NOW())<=2,YEAR(NOW())-1,YEAR(NOW())) and quarter=QUARTER(DATE_SUB(NOW(),INTERVAL 2 QUARTER)) then a.amount else null end) as q2 ,
    sum(case when a.year=IF(QUARTER(NOW())<=1,YEAR(NOW())-1,YEAR(NOW())) and quarter=QUARTER(DATE_SUB(NOW(),INTERVAL 1 QUARTER)) then a.amount else null end) as q3 ,
    sum(case when a.year=YEAR(NOW()) and quarter=QUARTER(NOW()) then a.amount else null end) as q4,
    sum(case when a.year=YEAR(NOW()) then a.amount else null end) as amount1,