在某些数据库中有交叉表,但在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]另外散分:
1。坚决不做地主富农
2。庆祝本月升三星
3。庆祝今年的第五朵红花
4。庆祝本月大版得分过万
感谢大版主的支持,感谢小版主的支持,感谢CSDN朋友的支持。
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 C1UNION
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
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
我在MySQL Query Browser里测试没问题的..
放到mysql客户端里运行...total就变成0了..
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
*/