一个复杂sql 如何 写 (有点像行列置换)表a  (id int, username varchar(255), month int, val  float)业务简单描述:
表a 纪录是用户1年的12个月各月的资金数额.  id共有30万
基本数据如下
id username  month val 
1  jr        1      232.1
1  jr        2      23.1
1  jr        3      2.1
1  jr        4      232.1
1  jr        5      23.1
1  jr        6      2.1
1  jr        7      232.1
1  jr        8      23.1
1  jr        9      2.1
1  jr        10     232.1
1  jr        11     23.1
1  jr        12     100
2  lj        1      5.33
......................
根据业务 要求 想从这个表得到下面的报表
id username  1月  2月 3月 4月 5月  6月 7月 8月 9月  10月 11月 12月
1  jr        232.1 23.1 2.1 ....................................
2  lj        ..................................................   
.
.
.
.
.
最后 还有一个汇总
             1月所有资金总额 2月所有资金总额  3月所有资金总额 4月所有资金总额.。12月资金总额
 
   
这个sql 该如何 写?(我写了部分 select id ,username,  case when month=1 then val;.... 
但最后一行的汇总不知道如何评入到这个sql里

解决方案 »

  1.   

    第一部分呢 已经得到sql 
     select id,userName, 
    sum(case when mon=1 then val else 0 end) as Jan,
    sum(case when mon=2 then val else 0 end) as Feb,
    sum(case when mon=3 then val else 0 end) as Mar
    from aa
    group by id;现在 最后 还有一个汇总
      1月所有资金总额 2月所有资金总额 3月所有资金总额 4月所有资金总额.。12月资金总额
      
    这个不知道该如何做
      

  2.   

    结果搞定
    select id,userName, 
    sum(case when mon=1 then val else 0 end) as Jan,
    sum(case when mon=2 then val else 0 end) as Feb,
    sum(case when mon=3 then val else 0 end) as Mar
    from aa
    group by id
    union all
    select null,null,sum(Jan),sum(Feb),sum(Mar) from
    (select id,userName, 
    sum(case when mon=1 then val else 0 end) as Jan,
    sum(case when mon=2 then val else 0 end) as Feb,
    sum(case when mon=3 then val else 0 end) as Mar
    from aa
    group by id
    ) a; 这里需要特别汇总下数据select null,null,sum(Jan),sum(Feb),sum(Mar) from
    (select id,userName, 
    sum(case when mon=1 then val else 0 end) as Jan,
    sum(case when mon=2 then val else 0 end) as Feb,
    sum(case when mon=3 then val else 0 end) as Mar
    from aa
    group by id
    ) aok 补齐整所有12个月。 这个case就over
    欢迎提供更简洁的sql
      

  3.   

    sum(case when mon=1 then val else 0 end) as Jan,
    sum(case when mon=2 then val else 0 end) as Feb,
    sum(case when mon=3 then val else 0 end) as Mar,
    sum(case when mon=4 then val else 0 end) as Apr,
    sum(case when mon=5 then val else 0 end) as May,
    sum(case when mon=6 then val else 0 end) as Jun,
    sum(case when mon=7 then val else 0 end) as Jul,
    sum(case when mon=8 then val else 0 end) as Aug,
    sum(case when mon=9 then val else 0 end) as Sep,
    sum(case when mon=10 then val else 0 end) as Oct,
    sum(case when mon=11 then val else 0 end) as Nov,
    sum(case when mon=12 then val else 0 end) as 'Dec'-----------这里mysql默认关键字 dec不能出现 ,所以必须加上 单引号 。  搞了好半天。
     
      

  4.   

    如何用SQL语句实现行列转换 收藏如何用SQL语句实现行列转换行列转换是数据库系统中经常遇到的一个需求,在数据库设计时,为了适合数据的累积存储,往往采用直接记录的方式,而在展示数据时,则希望整理所有记录并且转置显示。图9.1展示了行列转换的功能。
     
    图9.1  行列转换的需求分析这个需求,可以发现希望做的是找出具有相同部门的记录,并根据其材料的值累加数量。如果手动来写的话,最终希望得到的是下面这样的SQL语句:select 部门,
    sum(case 材料 when '材料1' then 数量 else 0 end) [材料1],
    sum(case 材料 when '材料2' then 数量 else 0 end) [材料2],
    sum(case 材料 when '材料3' then 数量 else 0 end) [材料3] 
    from 部门耗材 
    group by 部门这是一个非常简单的查询语句,并且执行结果恰好就是希望得到的结果,但问题是,如何得知原表中究竟包含几种材料呢?显然,根据上述的SQL语句,得到的结果永远只能统计3种材料的消耗,这时候就需要动态地根据实际材料数目来得到查询语句。代码9-1实现了一个动态行列转换。代码9-1  动态行列转换: Transfer.sql--申明一个字符串变量,以供动态拼装
    declare @sql varchar(8000)
    --拼装SQL命令
    set @sql = 'select Department'
    --动态地获得材料,为每个材料构建一个列
    select @sql = @sql + ',sum(case Item when '''+Item+'''
    then Number else 0 end) ['+Item+']'
    from (select distinct Item from DepartCost) as a
    --最终加上选择源和GROUP BY语句
    select @sql = @sql+' from DepartCost group by Department'
    --执行SQL命令
    exec(@sql)为了书写方便,表名和列名都没有采用中文名字。建议读者在进行数据库设计时,尽量避免直接使用汉字,可以采用拼音或者缩写的方式来替代。下面是这个SQL命令的执行结果:Department
    Item1
    Item2
    Item3F1
    3
    1
    2F2
    0
    2
    1F3
    1
    0
    1
    这样的解决方案仍然有不少缺陷。主要有两点:第一是动态SQL命令执行效率往往不高,因为动态拼装的原因,导致数据库管理系统无法对这样的命令进行优化;第二是这样的SQL命令必须先确定其长度限制,而动态SQL命令的长度往往根据实际表的内容而改变,所以这个命令无法保证100%能够运行。
     
    答案
     
    行列转换的SQL命令通常需要依靠动态的SQL语句,具体的实现方法请参考本节的问题分
      

  5.   

    现整理解法如下:
    数据样本:
    现整理解法如下:数据样本:create table tx(
     id int primary key,
     c1 char(2),
     c2 char(2),
     c3 int
    );insert into tx values
    (1 ,'A1','B1',9),
    (2 ,'A2','B1',7),
    (3 ,'A3','B1',4),
    (4 ,'A4','B1',2),
    (5 ,'A1','B2',2),
    (6 ,'A2','B2',9),
    (7 ,'A3','B2',8),
    (8 ,'A4','B2',5),
    (9 ,'A1','B3',1),
    (10 ,'A2','B3',8),
    (11 ,'A3','B3',8),
    (12 ,'A4','B3',6),
    (13 ,'A1','B4',8),
    (14 ,'A2','B4',2),
    (15 ,'A3','B4',6),
    (16 ,'A4','B4',9),
    (17 ,'A1','B4',3),
    (18 ,'A2','B4',5),
    (19 ,'A3','B4',2),
    (20 ,'A4','B4',5); mysql> select * from tx;
    +----+------+------+------+
    | id | c1   | c2   | c3   |
    +----+------+------+------+
    |  1 | A1   | B1   |    9 |
    |  2 | A2   | B1   |    7 |
    |  3 | A3   | B1   |    4 |
    |  4 | A4   | B1   |    2 |
    |  5 | A1   | B2   |    2 |
    |  6 | A2   | B2   |    9 |
    |  7 | A3   | B2   |    8 |
    |  8 | A4   | B2   |    5 |
    |  9 | A1   | B3   |    1 |
    | 10 | A2   | B3   |    8 |
    | 11 | A3   | B3   |    8 |
    | 12 | A4   | B3   |    6 |
    | 13 | A1   | B4   |    8 |
    | 14 | A2   | B4   |    2 |
    | 15 | A3   | B4   |    6 |
    | 16 | A4   | B4   |    9 |
    | 17 | A1   | B4   |    3 |
    | 18 | A2   | B4   |    5 |
    | 19 | A3   | B4   |    2 |
    | 20 | A4   | B4   |    5 |
    +----+------+------+------+
    20 rows in set (0.00 sec)mysql>期望结果+------+-----+-----+-----+-----+------+
    |C1    |B1   |B2   |B3   |B4   |Total |
    +------+-----+-----+-----+-----+------+
    |A1    |9    |2    |1    |11   |23    |
    |A2    |7    |9    |8    |7    |31    |
    |A3    |4    |8    |8    |8    |28    |
    |A4    |2    |5    |6    |14   |27    |
    |Total |22   |24   |23   |40   |109   |
    +------+-----+-----+-----+-----+------+1. 利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为 Total现整理解法如下:
    数据样本:mysql> SELECT
        ->     IFNULL(c1,'total') AS total,
        ->     SUM(IF(c2='B1',c3,0)) AS B1,
        ->     SUM(IF(c2='B2',c3,0)) AS B2,
        ->     SUM(IF(c2='B3',c3,0)) AS B3,
        ->     SUM(IF(c2='B4',c3,0)) AS B4,
        ->     SUM(IF(c2='total',c3,0)) AS total
        -> FROM (
        ->     SELECT c1,IFNULL(c2,'total') AS c2,SUM(c3) AS c3
        ->     FROM tx
        ->     GROUP BY c1,c2
        ->     WITH ROLLUP
        ->     HAVING c1 IS NOT NULL
        -> ) AS A
        -> GROUP BY c1
        -> WITH ROLLUP;
    +-------+------+------+------+------+-------+
    | total | B1   | B2   | B3   | B4   | total |
    +-------+------+------+------+------+-------+
    | A1    |    9 |    2 |    1 |   11 |    23 |
    | A2    |    7 |    9 |    8 |    7 |    31 |
    | A3    |    4 |    8 |    8 |    8 |    28 |
    | A4    |    2 |    5 |    6 |   14 |    27 |
    | total |   22 |   24 |   23 |   40 |   109 |
    +-------+------+------+------+------+-------+
    5 rows in set, 1 warning (0.00 sec)2. 利用SUM(IF()) 生成列 + union   生成汇总行,并利用 IFNULL将汇总行标题显示为 Total
    mysql> select c1,
        -> sum(if(c2='B1',C3,0)) AS B1,
        -> sum(if(c2='B2',C3,0)) AS B2,
        -> sum(if(c2='B3',C3,0)) AS B3,
        -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
        -> from tx
        -> group by C1
        -> union  
        -> SELECT 'TOTAL',sum(if(c2='B1',C3,0)) AS B1,
        -> sum(if(c2='B2',C3,0)) AS B2,
        -> sum(if(c2='B3',C3,0)) AS B3,
        -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) FROM TX
        -> ;
    +-------+------+------+------+------+-------+
    | c1    | B1   | B2   | B3   | B4   | TOTAL |
    +-------+------+------+------+------+-------+
    | A1    |    9 |    2 |    1 |   11 |    23 |
    | A2    |    7 |    9 |    8 |    7 |    31 |
    | A3    |    4 |    8 |    8 |    8 |    28 |
    | A4    |    2 |    5 |    6 |   14 |    27 |
    | TOTAL |   22 |   24 |   23 |   40 |   109 |
    +-------+------+------+------+------+-------+
    5 rows in set (0.00 sec)数据样本:mysql> 3.  利用SUM(IF()) 生成列,直接生成结果不再利用子查询
    mysql> select ifnull(c1,'total'),
        -> sum(if(c2='B1',C3,0)) AS B1,
        -> sum(if(c2='B2',C3,0)) AS B2,
        -> sum(if(c2='B3',C3,0)) AS B3,
        -> sum(if(c2='B4',C3,0)) AS B4,SUM(C3) AS TOTAL
        -> from tx
        -> group by C1 with rollup ;
    +--------------------+------+------+------+------+-------+
    | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |
    +--------------------+------+------+------+------+-------+
    | A1                 |    9 |    2 |    1 |   11 |    23 |
    | A2                 |    7 |    9 |    8 |    7 |    31 |
    | A3                 |    4 |    8 |    8 |    8 |    28 |
    | A4                 |    2 |    5 |    6 |   14 |    27 |
    | total              |   22 |   24 |   23 |   40 |   109 |
    +--------------------+------+------+------+------+-------+
    5 rows in set (0.00 sec)mysql>
    4. 动态,适用于列不确定情况,mysql> SET @EE='';
    mysql> SELECT @EE:=CONCAT(@EE,'SUM(IF(C2=\'',C2,'\'',',C3,0)) AS ',C2,',') FROM (SELECT DISTINCT C2 FROM TX) A; mysql> SET @QQ=CONCAT('SELECT ifnull(c1,\'total\'),',LEFT(@EE,LENGTH(@EE)-1),' ,SUM(C3) AS TOTAL FROM TX GROUP BY C1 WITH ROLLUP');
    Query OK, 0 rows affected (0.00 sec)mysql> PREPARE stmt2 FROM @QQ;
    Query OK, 0 rows affected (0.00 sec)
    Statement preparedmysql> EXECUTE stmt2;
    +--------------------+------+------+------+------+-------+
    | ifnull(c1,'total') | B1   | B2   | B3   | B4   | TOTAL |
    +--------------------+------+------+------+------+-------+
    | A1                 |    9 |    2 |    1 |   11 |    23 |
    | A2                 |    7 |    9 |    8 |    7 |    31 |
    | A3                 |    4 |    8 |    8 |    8 |    28 |
    | A4                 |    2 |    5 |    6 |   14 |    27 |
    | total              |   22 |   24 |   23 |   40 |   109 |
    +--------------------+------+------+------+------+-------+
    5 rows in set (0.00 sec)
    mysql>以上均由网友  liangCK , wwwwb , WWWWA , dap570 提供, 再次感谢他们的支持。其实数据库中也可以用 CASE WHEN / DECODE 代替 IFhttp://edu.codepub.com/2010/0531/23122_4.php
      

  6.   

    你可以用UNION ALL加上汇总,也可以用ROLLUP实现。