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

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

mysql交叉表查询解决方案整理

交叉表是一种常用的分类汇总查询.使用交叉表查询,可以显示表中某个字段的汇总值,并将它们分组,其中一组列在数据表的左侧,另一组列在数据表的上部.行和列的交叉处可以对数据进行多种汇总计算,如:求和.平均值 ...

mysql 行列动态转换(列联表，交叉表)

mysql 行列动态转换(列联表,交叉表) (1)动态,适用于列不确定情况 create table table_name( id int primary key, col1 char(2), col ...

mysql向表中某字段后追加一段字符串:

mysql向表中某字段后追加一段字符串:update table_name set field=CONCAT(field,'',str) mysql 向表中某字段前加字符串update table_n ...

oracle与mysql创建表时的区别

oracle创建表时,不支持在建表时同时增加字段注释.故采用以下方式: #创建表CREATE TABLE predict_data as ( id integer ), mid ), time dat ...

mysql数据库表的自增主键号不规律，重新排列

mysql数据库表的自增主键ID乱了,需要重新排序. 原理:删除原有的自增ID,重新建立新的自增ID. 1.删除原有主键: ALTER TABLE `table_name` DROP `id`; 2. ...

基于表的数据字典构造MySQL建表语句

表的数据字典格式如下: 如果手动写MySQL建表语句,确认麻烦,还不能保证书写一定正确. 写了个Perl脚本,可快速构造MySQL脚本语句. 脚本如下: #!/usr/bin/perl use str ...

创建ASP.NET Core MVC应用程序(3)-基于Entity Framework Core(Code First)创建MySQL数据库表

创建ASP.NET Core MVC应用程序(3)-基于Entity Framework Core(Code First)创建MySQL数据库表 创建数据模型类(POCO类) 在Models文件夹下添 ...

用户中心mysql数据库表结构的脚本

/* Navicat MySQL Data Transfer Source Server : rm-m5e3xn7k26i026e75o.mysql.rds.aliyuncs.com Source S ...

MySQL InnoDB表--BTree基本数据结构

MySQL InnoDB表是索引组织表这一点应该是每一个学习MySQL的人都会首先学到的知识,这代表这表中的数据是按照主键顺序存储,也就是说BTree的叶子节点存储了所有该行的数据. 我最开始是搞Or ...

随机推荐

LocalDB 静默安装

cmd命令:msiexec /i SqlLocalDB.msi /qn IACCEPTSQLLOCALDBLICENSETERMS=YES 注意:需要以管理员身份运行

Sublime Text编辑工具带有 PEP 8 格式检测插件

Sublime Text编辑工具带有 PEP 8 格式检测插件

【BZOJ】【2818】Gcd

欧拉函数/莫比乌斯函数 嗯……跟2190很像的一道题,在上道题的基础上我们很容易就想到先求出gcd(x,y)==1的组,然后再让x*=prime[i],y*=prime[i]这样它们的最大公约数就是p ...

Samba 服务器介绍

Samba是在Linux和UNIX系统上实现SMB协议的一个免费软件,由服务器及客户端程序构成.SMB(Server Messages Block,信息服务块)是一种在局域网上共享文件和打印机的一种通 ...

android 仿EF看视频弹出练习功能

第一次看到英孚iPad上的视频练习,感觉好有创意,让我们学习英语起来更加的有激情.而且不仅仅是在EF上有.相信在其它的学校肯定也会有的. 以上也我尝试把它写出来的原因了. 以下让我们来看看它的效果是怎 ...

python基础(1)-helloworld

搭建python运行环境 下载python安装包 配置好环境变量,可参考此链接 第一个helloworld #单行注释 '''多行注释''' """多行注释"& ...

mysql性能调整三板斧

大意是,用2/8原则,多快好省的解决大部分事情.所以三板斧,仅限整体调整,不牵扯具体细节. 1.innodb 使用innodb引擎 2.innodb_buffer_pool 调整和innodb有关的参 ...

Codeforces Round #427 (Div. 2) Problem C Star sky (Codeforces 835C) - 前缀和

The Cartesian coordinate system is set in the sky. There you can see n stars, the i-th has coordinat ...

(剑指Offer)面试题4：替换空格

题目: 请实现一个函数,把字符串中的每个空格替换成“%20”,例如输入“We are happy”,则输出“We%20are%20happy”. 思路: 背景: 在网络编程中,如果URL参数中含有特殊 ...