MySQL一个表存储另外一个表的多个id集合,如何关联查询
现存在两表(students学生表、honor荣誉表),表结构和相关数据如下
·
一、两张表
现存在两表(students学生表、honor荣誉表),表结构和相关数据如下:
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for honors
-- ----------------------------
DROP TABLE IF EXISTS `honors`;
CREATE TABLE `honors` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`honor_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '荣誉名称',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 7 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of honors
-- ----------------------------
INSERT INTO `honors` VALUES (1, '最强干饭王');
INSERT INTO `honors` VALUES (2, '最美班长');
INSERT INTO `honors` VALUES (3, '学生奖学金一等奖');
INSERT INTO `honors` VALUES (4, '国家奖学金');
INSERT INTO `honors` VALUES (5, '省年度大学生');
INSERT INTO `honors` VALUES (6, '篮球小王子');
SET FOREIGN_KEY_CHECKS = 1;
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for students
-- ----------------------------
DROP TABLE IF EXISTS `students`;
CREATE TABLE `students` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`student_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '学生姓名',
`honor_id` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '荣誉id',
PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 3 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of students
-- ----------------------------
INSERT INTO `students` VALUES (1, '张大卫', '1,2,3,4');
INSERT INTO `students` VALUES (2, '陈小楠', '3,4,5,6');
SET FOREIGN_KEY_CHECKS = 1;
学生表
荣誉表
二、需求
请编写一条SQL语句:将每个学生的学生姓名、所获得的荣誉显示出来
1、假如用左外连接:
SELECT s.student_name,h.honor_name from students s LEFT JOIN honors h on s.id = h.id
结果显然不对,只查到了一条,不符合需求
2、假如使用右外连接:
SELECT s.student_name,h.honor_name from students s RIGHT JOIN honors h on s.id = h.id
结果更不对!显然更是大错特错
3、全连接
直接报错!这个就不用试了!!
三、问题分析
由于外连接是根据左右表的相等数据去连接查询的。但是在这两个表中,学生表的honor_id字段是个集合,所以在连接查询的时候,只会匹配到第一个数据,而后面的数值匹配不到。
四、FIND_IN_SET函数
函数格式:FIND_IN_SET(str,strlist)
参数: str:表示你要查询的字符串,通常是但一个的数据格式,即荣誉表id
strlist:表示要查询的集合,参数以”,”分隔 如 (1,2,6,8),即学生表荣誉id集合
功能:查询字段(strlist)中包含(str)的结果,返回结果为null或记录
因此我们这条语句就可以这么写
SELECT h.honor_name,s.student_name FROM students s LEFT JOIN honors h ON FIND_IN_SET( h.id, s.honor_id )
这样子,我们就完成了!响应的荣誉和学生都对应了!
五、GROUP_CONCAT(expr)函数
这个函数简单点说,就是将查询出来的一列数据通过英文的逗号拼接成一行。举个例子:
SELECT GROUP_CONCAT(honor_name) AS honorName FROM honors
运行结果:
我们将这条语句运用到我们这个方法上,改进的代码就是
SELECT
( SELECT GROUP_CONCAT( honor_name) FROM honors h WHERE FIND_IN_SET( h.id, s.honor_id ) ) as honor ,
s.student_name as name
FROM
students s;
我们就可以看到最终的效果了:
这样子看,就很舒服了!希望对你有所帮助。
更多推荐
已为社区贡献1条内容
所有评论(0)