转 http://bbs.csdn.net/topics/310045927
在某些数据库中有交叉表，但在mysql中却没有这个功能，但网上看到有不少朋友想找出一个解决方法，特集思广义。无论对错皆有分。
数据样本：
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);
结果 (可不带行/列汇总)
[code=batchfile]+------+-----+-----+-----+-----+------+
|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 |
+------+-----+-----+-----+-----+------+[/code]
方法一：
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)
方法二：
静态：
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
方法三：
动态：
set @ee='';
select @ee:=concat(@ee,'sum(if(c2=\'',c2,'\'',',c3,0)) as ',c2,',') from
(select distinct c2 from tx) a;
set @qq=concat('select ifnull(c1,\'total\'),',left(@ee,length(@ee)-1),' ,sum(c3) as total from tx group by c1 with rollup');
prepare stmt2 from @qq;
execute stmt2;
方法四：
--木有合计,在sql server下测试通过
if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);
insert into #tx (id,c1,c2,c3) values (1 ,'a1','b1',9)
insert into #tx (id,c1,c2,c3) values (2 ,'a2','b1',7)
insert into #tx (id,c1,c2,c3) values (3 ,'a3','b1',4)
insert into #tx (id,c1,c2,c3) values (4 ,'a4','b1',2)
insert into #tx (id,c1,c2,c3) values (5 ,'a1','b2',2)
insert into #tx (id,c1,c2,c3) values (6 ,'a2','b2',9)
insert into #tx (id,c1,c2,c3) values (7 ,'a3','b2',8)
insert into #tx (id,c1,c2,c3) values (8 ,'a4','b2',5)
insert into #tx (id,c1,c2,c3) values (9 ,'a1','b3',1)
insert into #tx (id,c1,c2,c3) values (10 ,'a2','b3',8)
insert into #tx (id,c1,c2,c3) values (11 ,'a3','b3',8)
insert into #tx (id,c1,c2,c3) values (12 ,'a4','b3',6)
insert into #tx (id,c1,c2,c3) values (13 ,'a1','b4',8)
insert into #tx (id,c1,c2,c3) values (14 ,'a2','b4',2)
insert into #tx (id,c1,c2,c3) values (15 ,'a3','b4',6)
insert into #tx (id,c1,c2,c3) values (16 ,'a4','b4',9)
insert into #tx (id,c1,c2,c3) values (17 ,'a1','b4',3)
insert into #tx (id,c1,c2,c3) values (18 ,'a2','b4',5)
insert into #tx (id,c1,c2,c3) values (19 ,'a3','b4',2)
insert into #tx (id,c1,c2,c3) values (20 ,'a4','b4',5)
select c1
,sum(case
when c2='b1' then c3
else 0
end) as [b1]
,sum(case
when c2='b2' then c3
else 0
end) as [b2]
,sum(case
when c2='b3' then c3
else 0
end) as [b3]
,sum(case
when c2='b4' then c3
else 0
end) as [b4]
from #tx
group by c1
方法五：
--动态的话
if object_id('tempdb..#tx') is not null drop table #tx
go
create table #tx(
id int primary key,
c1 char(2),
c2 char(2),
c3 int
);
insert into #tx (id,c1,c2,c3) values (1 ,'a1','b1',9)
insert into #tx (id,c1,c2,c3) values (2 ,'a2','b1',7)
insert into #tx (id,c1,c2,c3) values (3 ,'a3','b1',4)
insert into #tx (id,c1,c2,c3) values (4 ,'a4','b1',2)
insert into #tx (id,c1,c2,c3) values (5 ,'a1','b2',2)
insert into #tx (id,c1,c2,c3) values (6 ,'a2','b2',9)
insert into #tx (id,c1,c2,c3) values (7 ,'a3','b2',8)
insert into #tx (id,c1,c2,c3) values (8 ,'a4','b2',5)
insert into #tx (id,c1,c2,c3) values (9 ,'a1','b3',1)
insert into #tx (id,c1,c2,c3) values (10 ,'a2','b3',8)
insert into #tx (id,c1,c2,c3) values (11 ,'a3','b3',8)
insert into #tx (id,c1,c2,c3) values (12 ,'a4','b3',6)
insert into #tx (id,c1,c2,c3) values (13 ,'a1','b4',8)
insert into #tx (id,c1,c2,c3) values (14 ,'a2','b4',2)
insert into #tx (id,c1,c2,c3) values (15 ,'a3','b4',6)
insert into #tx (id,c1,c2,c3) values (16 ,'a4','b4',9)
insert into #tx (id,c1,c2,c3) values (17 ,'a1','b4',3)
insert into #tx (id,c1,c2,c3) values (18 ,'a2','b4',5)
insert into #tx (id,c1,c2,c3) values (19 ,'a3','b4',2)
insert into #tx (id,c1,c2,c3) values (20 ,'a4','b4',5)
select * from #tx
declare @sql varchar(8000)
set @sql='select c1,'
select @sql=@sql+'sum(case when c2='''+cast(c2 as varchar(10))+''' then c3 else 0 end)['+cast(c2 as varchar(10))+'],'
from (select distinct c2 from #tx ) a
print(@sql)
set @sql=left(@sql,len(@sql)-1)+' from #tx group by c1'
exec(@sql)
if object_id('tempdb..#tx') is not null drop table #tx
/*
a1 9 2 1 11
a2 7 9 8 7
a3 4 8 8 8
a4 2 5 6 14
*/
测试
1、
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、
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、
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 prepared
mysql> 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>
5、
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 prepared
mysql> 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>