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

ACMAIN_CHM IT  2009-05-30 11:54:30
加精

在某些数据库中有交叉表,但在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朋友的支持。
...全文
6158 60 点赞 打赏 收藏 举报
写回复
60 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
yanfangphp 2012-07-06
http://topic.csdn.net/u/20120706/15/69eef30f-4386-4374-9126-d1a37e0ff13b.html 各位帮帮忙看看这个的列转行吧
  • 打赏
  • 举报
回复
yanfangphp 2012-07-04
受教了。。。
  • 打赏
  • 举报
回复
Diana_24 2012-05-16
Mark一下,最近正好在学习!谢谢!
  • 打赏
  • 举报
回复
GIS_Cloud 2011-10-13
GOOD,收藏了,学习中
  • 打赏
  • 举报
回复
lfcms 2010-11-12
这逻辑 天哪。。 没想到可以直接SQL出来。
  • 打赏
  • 举报
回复
wangqiongbo 2010-10-19
恩,不错,以前俺都不知道还可以这样查找。
  • 打赏
  • 举报
回复
zouyx317 2010-05-17
好贴,虽然已结,还得标记!
  • 打赏
  • 举报
回复
antony1029 2010-01-14
jf
  • 打赏
  • 举报
回复
lovezx1028 2010-01-03
ding
  • 打赏
  • 举报
回复
config_man 2009-11-10
学习!!!
  • 打赏
  • 举报
回复
amossavez 2009-06-11
进来学习的!!!
  • 打赏
  • 举报
回复
oraclelogan 2009-06-10
[Quote=引用 46 楼 ACMAIN_CHM 的回复:]
整理一下

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

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,
-> …
[/Quote]

学习,原来楼主是mysql高手啊!学习了!
  • 打赏
  • 举报
回复
ACMAIN_CHM 2009-06-07

整理一下

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

4楼 梁版

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)



5楼 WWWB
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>


6楼 WWWA
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>


============================================================
动态
============================================================
8楼 WWWA

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>


还有两个MS SQL SERVER的解法,稍改一下就可以用在MySQL中了。
18楼 dap570
20楼 dap570
  • 打赏
  • 举报
回复
jack_ailly 2009-06-07
学习
  • 打赏
  • 举报
回复
nnccuuww001 2009-06-07
学习了~~·
  • 打赏
  • 举报
回复
seamanhy 2009-06-02
好久没上csdn了,先回帖,再看。
  • 打赏
  • 举报
回复
融拓 2009-06-02
学习下
  • 打赏
  • 举报
回复
支持一下
  • 打赏
  • 举报
回复
seamanhy 2009-06-02
最近也在学MySql,学习了
  • 打赏
  • 举报
回复
加载更多回复
相关推荐
发帖
MySQL
加入

5.5w+

社区成员

MySQL相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2009-05-30 11:54
社区公告
暂无公告