join行转列，union列转行

1、join行转列

成绩表grades1，添加数据

CREATE TABLE `grades1` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `subject` varchar(20) DEFAULT NULL COMMENT '学科',
  `score` int(4) DEFAULT NULL COMMENT '成绩',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (1, 'lyy', '英语', 100);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (2, 'lyy', '数学', 98);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (3, 'lyy', '语文', 97);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (4, 'gss', '英语', 89);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (5, 'gss', '数学', 88);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (6, 'gss', '语文', 87);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (7, 'liming', '英语', 79);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (8, 'liming', '数学', 77);
INSERT INTO `grades1`(`id`, `name`, `subject`, `score`) VALUES (9, 'liming', '语文', 75);

表grades1原数据

查询sql

-- 1.使用 join on 查询
SELECT yw.name,yw.score '语文',sx.score '数学',yy.score '英语'
FROM
(SELECT NAME,score FROM grades1 WHERE SUBJECT='语文') yw JOIN
(SELECT NAME,score FROM grades1 WHERE SUBJECT='数学') sx JOIN
(SELECT NAME,score FROM grades1 WHERE SUBJECT='英语') yy
ON yw.name=sx.name AND sx.name=yy.name

-- 2.使用 join using 查询
SELECT yw.name,yw.score '语文',sx.score '数学',yy.score '英语'
FROM
(SELECT NAME,score FROM grades1 WHERE SUBJECT='语文') yw JOIN
(SELECT NAME,score FROM grades1 WHERE SUBJECT='数学') sx USING(NAME) JOIN
(SELECT NAME,score FROM grades1 WHERE SUBJECT='英语') yy USING(NAME)

查询结果

注意：以上join连接语句使用了join buffer缓冲区进行查询，可以使用执行计划EXPLAIN进行查看

2、union列转行

成绩表grades2，添加数据

CREATE TABLE `grades2` (
  `id` int(4) NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20) DEFAULT NULL COMMENT '姓名',
  `语文` int(4) DEFAULT NULL COMMENT '语文',
  `数学` int(4) DEFAULT NULL COMMENT '数学',
  `英语` int(4) DEFAULT NULL COMMENT '英语',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

INSERT INTO `grades2`(`id`, `name`, `语文`, `数学`, `英语`) VALUES (1, 'lyy', 88, 89, 87);
INSERT INTO `grades2`(`id`, `name`, `语文`, `数学`, `英语`) VALUES (2, 'gss', 93, 92, 91);
INSERT INTO `grades2`(`id`, `name`, `语文`, `数学`, `英语`) VALUES (3, 'liu', 99, 88, 77);

表grades2原数据

查询sql

SELECT NAME,'语文' SUBJECT,语文 score FROM grades2  -- '语文'，该列为自加列subject；语文，该列为成绩score
UNION ALL
SELECT NAME,'数学' SUBJECT,数学 score FROM grades2
UNION ALL
SELECT NAME,'英语' SUBJECT,英语 score FROM grades2

注意：union与union all的区别：union在表关联的时候会自动进行去重操作，union all关联所有数据（包含重复）；
在没有去重需求时，建议使用union all，效率较高

查询结果