mysql某个字段的并集_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 forcj-- ----------------------------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 forcj_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');