关闭

有时候需要做报表时,如何将一个字段年份划分成12个月份呢?

155人阅读 评论(0) 收藏 举报
分类:
select v_short_name, 
       sum(case when c_time = '201501' then n_amount else 0 end) as Jan,
       sum(case when c_time = '201502' then n_amount else 0 end) as Feb,
       sum(case when c_time = '201503' then n_amount else 0 end) as Mar,
       sum(case when c_time = '201504' then n_amount else 0 end) as Apr,
       sum(case when c_time = '201505' then n_amount else 0 end) as May,
       sum(case when c_time = '201506' then n_amount else 0 end) as Jun,
       sum(case when c_time = '201507' then n_amount else 0 end) as Jul,
       sum(case when c_time = '201508' then n_amount else 0 end) as Aug,
       sum(case when c_time = '201509' then n_amount else 0 end) as Sep,
       sum(case when c_time = '201510' then n_amount else 0 end) as Oct,
       sum(case when c_time = '201511' then n_amount else 0 end) as Nov,
       sum(case when c_time = '201512' then n_amount else 0 end) as Decn,
       sum(n_amount) as Total 
  from XXX

 group by v_short_name;


这个代码可以实现这个功能。


上面的方法为静态,下面的方法为动态:

SET @asql='';
    SELECT DISTINCT c_time,
    @asql:=CONCAT(@asql,CONCAT('MAX(CASE DATE(c_time) WHEN \'',DATE(c_time),'\' THEN n_amount ELSE 0 END) AS `',DATE(c_time)),'`,')
    FROM 表名;
    SET @asql=CONCAT('SELECT v_short_name 'LEFT(@asql,LENGTH(@asql)-1),' FROM 表名GROUP BY v_short_name');
      SELECT @asql;
      PREPARE stml FROM @asql;
      EXECUTE stml;

0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:51888次
    • 积分:1027
    • 等级:
    • 排名:千里之外
    • 原创:49篇
    • 转载:28篇
    • 译文:0篇
    • 评论:8条