mysql 数据交叉不重复 mysql交叉表查询

在MySQL中实现交叉表查询1（静态交叉表）

一、什么是交叉表

交叉表查询是将来源于某个表中的字段进行分组，一组列在交叉表左侧，一组列在交叉表上部，并在交叉表行与列交叉处显示表中某个字段的各种计算值。比如：

create table stu(
    s_id int auto_increment,
    name char(20) not null default '',
    gender char(5) not null default '',
    age int not null default 0,
    class char(10) not null default '',
    primary key(s_id)
);

insert into stu(name,gender,age,class) 
values('张平','男',20,'物流1班'),('王刚','男',21,'物流1班'),('刘静','女',18,'物流1班'),
('张静静','女',21,'物流1班'),('刘涛','男',19,'物流1班'),('王晓刚','男',20,'营销2班'),
('王红','女',17,'营销2班'),('刘梅','女',19,'营销2班'),('张军','男',20,'营销2班'),
('张成','男',19,'营销2班'),('张晓媛','女',18,'营销2班'),('张华','男',21,'国贸1班'),
('王晓红','女',17,'国贸1班'),('张晓梅','女',19,'国贸1班'),('张涛','男',18,'国贸1班');
select * from stu;

根据class和gender两个字段进行分组查询，统计每个班级的男生和女生的平均分：

select class,gender,avg(age) from stu group by class,gender;

查询结果如下：

根据以上查询结果可以生成交叉表，其中class分组作为行，gender分组作为列，结果如下图所示：

使用交叉表查询来计算和重构数据，可以简化数据分析。交叉表查询计算数据的总和，平均值，计数及其他类型的统计，并将它们分组，一组列在数据表左侧作为交叉表的行字段，另一组列在数据表的顶端作为交叉表的列字段。

交叉表分为静态交叉表和动态交叉表。其中静态交叉表中的列是固定的，因此相对容易实现；而动态交叉表中的列需要动态生成。

二、静态交叉表的实现

MySQL不提供直接创建交叉表的命令，如果需要创建交叉表可以采用以下步骤：

step1 创建三张表，student（学生）、course（课程）、score（成绩），命令如下：

create table student(
    s_id int primary key auto_increment comment '学生学号',
    s_name char(20) comment '学生姓名',
    age int comment '学生年龄'
);

create table course(
    c_id int primary key auto_increment comment '课程编号',
    c_name char(20) comment '课程名称',
    credits int comment '课程学分'
);

create table score(
    s_id int comment '学生学号',
    c_id int comment '课程编号',
    score int comment '成绩',
    primary key(s_id,c_id)
);

step2 为以上三张表插入必要的数据，命令如下：

insert into student
values(1001,'张平',20),(1002,'王刚',21),(1003,'张静静',19),
(1004,'王涛',20),(1005,'王鹏飞',19);

insert into course
values(1,'数据库',4),(2,'数据结构',4),(3,'管理学',3),(4,'英语',4),(5,'电子商务',3);

insert into score 
values(1001,1,80),(1001,2,90),(1001,3,77),(1001,4,87),(1001,5,69),
(1002,1,87),(1002,2,67),(1002,3,78),(1002,4,98),(1002,5,78),
(1003,1,66),(1003,2,77),(1003,3,88),(1003,4,99),(1003,5,66),
(1005,1,81),(1005,2,83),(1005,3,62),(1005,4,68),(1005,5,72),
(1004,1,72),(1004,2,60),(1004,3,84),(1004,4,88),(1004,5,74);

step3 创建交叉表，显示每个学生每一门课程的分数，其中表的左侧显示学生姓名，每一门课程生成一列，在学生姓名和课程交叉处显示某个学生某门课程的成绩，命令如下：

select IFNULL(s_name,'总分') as '姓名',sum(if(c_name='数据库',score,0)) as '数据库',
sum(if(c_name='数据结构',score,0)) as '数据结构',
sum(if(c_name='管理学',score,0)) as '管理学',sum(if(c_name='英语',score,0)) as '英语',
sum(if(c_name='电子商务',score,0)) as '电子商务',sum(score) as '总分'
from (select s_name,c_name,score 
      from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id) tmp
group by s_name
with rollup;

说明：Mysql中的with rollup是用来在分组统计数据的基础上再进行统计汇总，即用来得到group by的汇总信息。查询结果如下图所示：

也可以用union生成汇总行，代码如下：

select s_name,sum(if(c_name='数据库',score,0)) as '数据库',
sum(if(c_name='数据结构',score,0)) as '数据结构',
sum(if(c_name='管理学',score,0)) as '管理学',sum(if(c_name='英语',score,0)) as '英语',
sum(if(c_name='电子商务',score,0)) as '电子商务',sum(score) as '总分'
from (select s_name,c_name,score 
      from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id) tmp
group by s_name
union
select '总分',sum(if(c_id=1,score,0)),sum(if(c_id=2,score,0)),sum(if(c_id=3,score,0)),
sum(if(c_id=4,score,0)),sum(if(c_id=5,score,0)),sum(score)
from score;

查询结果如下图所示：

也可以不使用子查询，语法格式如下：

select IFNULL(s_name,'总分') as '姓名',sum(if(c_name='数据库',score,0)) as '数据库',
sum(if(c_name='数据结构',score,0)) as '数据结构',
sum(if(c_name='管理学',score,0)) as '管理学',sum(if(c_name='英语',score,0)) as '英语',
sum(if(c_name='电子商务',score,0)) as '电子商务',sum(score) as '总分'
from student s,course c,score sc where s.s_id=sc.s_id and c.c_id=sc.c_id
group by s_name
with rollup;