Mysql 交集、并集、差集、行转列、列转行

1、并集: a和b所有元素的集合

-- 全连接 ==== 并集: a和b所有元素的集合
SELECT a.id id , a.user_name ausername, a.score ascore  , b.user_name busername ,b.score bscore FROM cj a LEFT JOIN cj_copy  b on a.id = b.id
UNION
SELECT a.id id , a.user_name ausername, a.score ascore  , b.user_name busername ,b.score bscore FROM cj a RIGHT  JOIN cj_copy  b on a.id = b.id

2、交集: 属于a又属于b的所有元素构成的集合

-- 等值连接 ==== 交集: 属于a又属于b的所有元素构成的集合
SELECT a.id id , a.user_name ausername, a.score ascore  , b.user_name busername ,b.score bscore FROM cj a INNER JOIN cj_copy  b on a.id = b.id

3、差集:所有属于a且不属于b的元素构成的集合

-- 非等值连接 ==== 差集:所有属于a且不属于b的元素构成的集合
SELECT a.id id , a.user_name ausername, a.score ascore  , b.user_name busername ,b.score bscore FROM cj a LEFT JOIN cj_copy  b on a.id = b.id WHERE b.id IS NULL

 4、行转列

-- 行转列
SELECT MAX(id),class_name,GROUP_CONCAT(user_name) from cj_copy GROUP BY class_name

5、列转行

SELECT a.id,    a.user_name,    a.class_name,
    substring_index( substring_index(a.score,    ',',    b.help_topic_id + 1),',' ,- 1) AS shareholder
FROM
    cj_copy a
JOIN mysql.help_topic b ON b.help_topic_id < (
    length(a.score) - length(REPLACE (a.score, ',', '')) + 1)

 

数据表

cj表)

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for cj
-- ----------------------------
DROP TABLE IF EXISTS `cj`;
CREATE TABLE `cj` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(10) DEFAULT NULL,
  `class_name` varchar(10) DEFAULT NULL,
  `score` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of cj
-- ----------------------------
INSERT INTO `cj` VALUES ('1', 'yang', '1', '90');
INSERT INTO `cj` VALUES ('4', 'zhou', '2', '90');
INSERT INTO `cj` VALUES ('5', 'qin', '2', '80');

cj_copy表)

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for cj_copy
-- ----------------------------
DROP TABLE IF EXISTS `cj_copy`;
CREATE TABLE `cj_copy` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(10) DEFAULT NULL,
  `class_name` varchar(10) DEFAULT NULL,
  `score` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of cj_copy
-- ----------------------------
INSERT INTO `cj_copy` VALUES ('2', 'wen', '1', '80,70,60');
INSERT INTO `cj_copy` VALUES ('3', 'jie', '1', '70');
INSERT INTO `cj_copy` VALUES ('5', 'qin', '2', '80');
INSERT INTO `cj_copy` VALUES ('6', 'qin', '3', '90');