[征集] MySQL交叉表解决方案及散分,该如何解决

2012-02-20

[征集] MySQL交叉表解决方案及散分在某些数据库中有交叉表，但在MySQL中却没有这个功能，但网上看到有不少朋

[征集] MySQL交叉表解决方案及散分

在某些数据库中有交叉表，但在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);

结果 (可不带行/列汇总)

BatchFile code

+------+-----+-----+-----+-----+------+|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。坚决不做地主富农
2。庆祝本月升三星
3。庆祝今年的第五朵红花
4。庆祝本月大版得分过万
感谢大版主的支持，感谢小版主的支持，感谢CSDN朋友的支持。

[解决办法]
特地来学习的！
[解决办法]

SQL 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,1095 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 codemysql>SELECT->IFNULL(c1,'total')AStotal,->SUM(IF(c2='B1',c3,0))ASB1,->SUM(IF(c2='B2',c3,0))ASB2,->SUM(IF(c2='B3',c3,0))ASB3,->SUM(IF(c2='B4',c3,0))ASB4,->SUM(IF(c2='total',c3,0))AStotal->FROM(->SELECTc1,IFNULL(c2,'total')ASc2,SUM(c3)ASc3->FROMtx->GROUPBYc1,c2->WITHROLLUP->HAVINGc1ISNOTNULL->)ASA->GROUPBYc1->WITHROLLUP;

"total","B1","B2","B3","B4","total" 
 
"A1",9,2,1,11,23"A2",7,9,8,7,31"…

[解决办法]
学习
[解决办法]
晕，好久没碰SQL，还以为出了个什么新东西，原来是汇总查询
[解决办法]
mark
[解决办法]
厉害 。。。。。。。
[解决办法]
SQL code--木有合计,在SQL Server下测试通过 if object_id('tempdb..#tx') is not null drop table #txgocreate 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       #txGROUP BY   c1
[解决办法]
jf
[解决办法]
好强悍的东东，都是高手，学习下

[解决办法]
我不懂什么哦 知道有个交叉查询  
谢谢楼主 受教了
[解决办法]
学习了，谢谢，
[解决办法]
xiaoxixiaoxi
[解决办法]
Mark!
[解决办法]
Mark!学习！
[解决办法]
学习了，帮顶下。
[解决办法]
有关于教师管理信息系统设计的数据库就好了
[解决办法]
学习！
[解决办法]
学习学习
[解决办法]
学习！
[解决办法]
恩，很好的行列转换典型例子，最近在研究mysql，先收下了，谢了！
------解决方案--------------------  
学习！
[解决办法]
俺是来学习的！
[解决办法]
恭喜
[解决办法]
学习了，谢谢楼主。
MYSQL里面的确没发现这个。
[解决办法]
学习了,顶一下.
[解决办法]
mark，先顶再学习
[解决办法]
支持一下
[解决办法]
学习下
[解决办法]
好久没上csdn了，先回帖，再看。
[解决办法]
最近也在学MySql，学习了
[解决办法]
学习了~~·
[解决办法]
学习
[解决办法]
探讨
整理一下 

============================================================ 
静态 
============================================================ 

4楼 梁版 


SQL codemysql> 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,
    -> …

[解决办法]
进来学习的！！！

热点排行

Mysql

[征集] MySQL交叉表解决方案及散分,该如何解决