一个复杂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里
)
表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里
)
解决方案 »
- 每次重启服务器之后,第一次运行存储过程总报错
- 本地数据库与远程数据库更新
- plpgsql 中的赋值也可以用‘=’,而不用‘:=’?
- mysql管理工具还有什么,sqlyog老是导错库
- 怎样把电子表格.xls导入mysql数据库
- 请教MySQL的数据库文件如何拷贝到服务器上
- 请问如何在MYSQL中如何输入单引号和双引号
- mysql中varchar字段怎么设置匹配大小写?
- 在mysql数据库中,最多可以创建多少个数据库?一个数据库中最多可以创建多少个表?一个数据库的大小范围怎么来设置?请高手指教!!!
- 简单的查询问题!
- 1个sql需要同时在 a b 机器上执行
- MYSQL数据库触发器的插入限制[急]
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月资金总额
这个不知道该如何做
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
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不能出现 ,所以必须加上 单引号 。 搞了好半天。
图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语句,具体的实现方法请参考本节的问题分
数据样本:
现整理解法如下:数据样本: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