MySQL交叉表

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/ACMAIN_CHM/article/details/4283943

在某些数据库中有交叉表,但在MySQL中却没有这个功能,但网上看到有不少朋友想找出一个解决方法,特发贴集思广义。
http://topic.csdn.net/u/20090530/23/0b782674-4b0b-4cf5-bc1a-e8914aaee5ab.html?96198
现整理解法如下:

数据样本:

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 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>

 

以上均由网友  liangCK , wwwwb , WWWWA , dap570 提供, 再次感谢他们的支持。

 

其实数据库中也可以用 CASE WHEN / DECODE 代替 IF

 

展开阅读全文

一个交叉表的问题,

02-12

科目代码 科目名称 部门 计划数 实际数rn0007.034 其他 供应公司 .0000000000 4500.0000000000rn0007.016 折旧费 汽运公司 .0000000000 36492.2800000000rn0007.018 水电费 汽运公司 .0000000000 3989.6800000000rn0007.004 职工教育经费 小包装车间 .0000000000 6000.0000000000rn0007.010 排污费(含赔偿) 小包装车间 .0000000000 24000.0000000000rn0007.012 差旅费 小包装车间 .0000000000 16540.0000000000rn0007.013 办公费 小包装车间 .0000000000 420.0000000000rn0007.014 运输费 小包装车间 .0000000000 176770.0000000000rn0007.015 保险费 小包装车间 .0000000000 63290.0200000000rn0007.016 折旧费 小包装车间 .0000000000 38.8000000000rn0007.020 车船使用税 小包装车间 .0000000000 2560.0000000000rn0007.028 业务招待费 小包装车间 .0000000000 179424.0000000000rn0007.034 其他 小包装车间 .0000000000 19689.4000000000rn0007.010 排污费(含赔偿) 安全生产处 .0000000000 109562.0000000000rn0007.013 办公费 安全生产处 .0000000000 1940.0000000000rn0007.032 聘请中介机构费 安全生产处 .0000000000 50000.0000000000rn0007.034 其他 安全生产处 .0000000000 10000.0000000000rn0007.012 差旅费 矿山资源处 .0000000000 3450.0000000000rn0007.013 办公费 矿山资源处 .0000000000 1334.0000000000rn0007.028 业务招待费 矿山资源处 833.3333330000 1760.0000000000rn0007.032 聘请中介机构费 矿山资源处 .0000000000 50000.0000000000rn0007.033 矿产资源补偿费 矿山资源处 .0000000000 10000.0000000000rn0007.034 其他 矿山资源处 .0000000000 4000.0000000000rn0007.034 其他 领导层 .0000000000 1990.0000000000rn0007.001 工资 公司办 .0000000000 106800.0000000000rn0007.004 职工教育经费 公司办 .0000000000 72360.0000000000rn0007.008 咨询费 公司办 .0000000000 200000.0000000000rn0007.013 办公费 公司办 .0000000000 146385.4000000000rn0007.014 运输费 公司办 .0000000000 29630.0000000000rn0007.016 折旧费 公司办 .0000000000 396.4600000000rn0007.017 修理费 公司办 .0000000000 90870.0000000000rn0007.028 业务招待费 公司办 625.0000000000 29486.0000000000rn0007.034 其他 公司办 .0000000000 110024.0000000000rn0007.016 折旧费 业务员 .0000000000 263.6200000000rn0007.013 办公费 销售公司 .0000000000 6.0000000000rn0007.034 其他 销售公司 .0000000000 2000.0000000000rn0007.004 职工教育经费 计财处 .0000000000 384.0000000000rn0007.012 差旅费 计财处 .0000000000 6546.0000000000rn0007.013 办公费 计财处 .0000000000 16349.6000000000rn0007.016 折旧费 计财处 .0000000000 2555.4800000000rn0007.028 业务招待费 计财处 4166.6666660000 60176.0000000000rn0007.034 其他 计财处 .0000000000 5200.0000000000rn0007.013 办公费 技术监督处 .0000000000 152.0000000000rn0007.016 折旧费 技术监督处 .0000000000 209.7600000000rn0007.028 业务招待费 技术监督处 416.6666660000 6392.0000000000rn0007.034 其他 技术监督处 .0000000000 24672.9800000000rn0007.013 办公费 保卫处 .0000000000 360.0000000000rn0007.028 业务招待费 保卫处 250.0000000000 926.0000000000rn0007.034 其他 保卫处 .0000000000 120148.8600000000rn0007.013 办公费 物业公司 .0000000000 174.0000000000rn0007.014 运输费 物业公司 .0000000000 8547.0000000000rn0007.016 折旧费 物业公司 .0000000000 59.5400000000rn0007.034 其他 物业公司 .0000000000 2203.7200000000rn0007.034 其他 润泽公司 .0000000000 30000.0000000000rn0007.012 差旅费 燃料处 .0000000000 20131.6400000000rn0007.013 办公费 燃料处 .0000000000 1347.0000000000rn0007.028 业务招待费 燃料处 1666.6666660000 3896.0000000000rn0007.013 办公费 工会 .0000000000 1179.2000000000rn0007.016 折旧费 工会 .0000000000 247.0000000000rn0007.028 业务招待费 工会 250.0000000000 4940.0000000000rn0007.012 差旅费 审计处 .0000000000 11348.0000000000rn0007.013 办公费 审计处 .0000000000 1341.2000000000rn0007.015 保险费 审计处 .0000000000 -1910577.5000000000rn0007.028 业务招待费 审计处 666.6666660000 16028.0000000000rn0007.032 聘请中介机构费 审计处 .0000000000 26000.0000000000rn0007.034 其他 审计处 .0000000000 240.0000000000rn0007.012 差旅费 结算中心 .0000000000 12202.0000000000rn0007.013 办公费 结算中心 .0000000000 3099.4000000000rn0007.016 折旧费 结算中心 .0000000000 142.5000000000rn0007.028 业务招待费 结算中心 2500.0000000000 60570.0000000000rn0007.034 其他 结算中心 .0000000000 336.0000000000rn0007.028 业务招待费 小包装车间 .0000000000rn0007.016 折旧费 公司办 .0000000000 396.4600000000rn0007.013 办公费 技术中心 .0000000000 7199.0000000000rn0007.016 折旧费 技术中心 .0000000000 83.9200000000rn0007.028 业务招待费 技术中心 833.3333330000 6288.0000000000rn0007.028 业务招待费 接待处 208.3333330000 13310.0000000000rnrnrn有以上的表 现在要做成如下:rn科目代码 科目名称 预算数 人事处 技术中心 接待处 ........rnrn0007.028 业务招待费 208.3333 13310.00rn0007.013 办公费 ........rn.rn.rn.rn.rn.rn请问以上如何做成交叉表 ???请指教一下,做不完不能过年,各位帮助一下!rn 论坛

没有更多推荐了,返回首页