mysql中子查询的嵌套
所解决的问题
写的代码
出现问题
系统反馈unknown column C_id having clause，为什么出现这样，我查了一下说是分组后的S_id字段不晓得筛选哪些C_id，如何解决这个问题
表
/*
Navicat MySQL Data Transfer
Source Server : MYSQL
Source Server Version : 50519
Source Host : localhost:3306
Source Database : jy33
Target Server Type : MYSQL
Target Server Version : 50519
File Encoding : 65001
Date: 2023-06-16 09:48:33
*/
SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for `courseinfos`
-- ----------------------------
DROP TABLE IF EXISTS `courseinfos`;
CREATE TABLE `courseinfos` (
`C_id` char(3) NOT NULL,
`C_name` char(20) NOT NULL,
`C_credit` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`C_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of courseinfos
-- ----------------------------
INSERT INTO `courseinfos` VALUES ('101', '计算机基础', '3');
INSERT INTO `courseinfos` VALUES ('102', 'C语言', '5');
INSERT INTO `courseinfos` VALUES ('103', '数据库', '4');
INSERT INTO `courseinfos` VALUES ('201', '离散数学', '4');
INSERT INTO `courseinfos` VALUES ('202', '计算机网络', '4');
INSERT INTO `courseinfos` VALUES ('301', '软件工程', '4');
-- ----------------------------
-- Table structure for `studentcourseinfos`
-- ----------------------------
DROP TABLE IF EXISTS `studentcourseinfos`;
CREATE TABLE `studentcourseinfos` (`S_id` char(7) NOT NULL,
`C_id` char(3) NOT NULL,
`Sc_grade` tinyint(4) NOT NULL DEFAULT '0',
KEY `s` (`S_id`),
KEY `c` (`C_id`),
CONSTRAINT `s` FOREIGN KEY (`S_id`) REFERENCES `studentinfos` (`S_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `c` FOREIGN KEY (`C_id`) REFERENCES `courseinfos` (`C_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of studentcourseinfos
-- ----------------------------
INSERT INTO `studentcourseinfos` VALUES ('4102101', '101', '80');
INSERT INTO `studentcourseinfos` VALUES ('4102101', '102', '89');
INSERT INTO `studentcourseinfos` VALUES ('4102101', '103', '78');
INSERT INTO `studentcourseinfos` VALUES ('4102102', '101', '57');
INSERT INTO `studentcourseinfos` VALUES ('4102102', '102', '67');
INSERT INTO `studentcourseinfos` VALUES ('4202103', '103', '90');
INSERT INTO `studentcourseinfos` VALUES ('4202110', '101', '85');
INSERT INTO `studentcourseinfos` VALUES ('4202110', '102', '91');
INSERT INTO `studentcourseinfos` VALUES ('4202221', '101', '69');
INSERT INTO `studentcourseinfos` VALUES ('4402130', '101', '78');
INSERT INTO `studentcourseinfos` VALUES ('4402130', '103', '74');
-- ----------------------------
-- Table structure for `studentinfos`
-- ----------------------------
DROP TABLE IF EXISTS `studentinfos`;
CREATE TABLE `studentinfos` (
`S_id` char(7) NOT NULL DEFAULT '',
`S_name` char(8) NOT NULL,
`S_sex` bit(1) NOT NULL DEFAULT b'1',
`S_major` varchar(20) NOT NULL,
`S_birth` datetime NOT NULL,PRIMARY KEY (`S_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of studentinfos
-- ----------------------------
INSERT INTO `studentinfos` VALUES ('4102101', '王林', '', '计算机', '1983-01-23 00:00:00');
INSERT INTO `studentinfos` VALUES ('4102102', '吴荣华', '', '计算机', '1984-03-28 00:00:00');
INSERT INTO `studentinfos` VALUES ('4202103', '张强', '', '电子', '1981-11-19 00:00:00');
INSERT INTO `studentinfos` VALUES ('4202110', '王向荣', '', '电子', '1980-12-09 00:00:00');
INSERT INTO `studentinfos` VALUES ('4202221', '李丽', '', '电子', '1982-07-30 00:00:00');
INSERT INTO `studentinfos` VALUES ('4302101', '刘明', '', '自控', '1982-10-18 00:00:00');
INSERT INTO `studentinfos` VALUES ('4402130', '叶凡', '', '数学', '1983-11-1800:00:00');
-- ----------------------------
-- View structure for `v_score1`
-- ----------------------------
DROP VIEW IF EXISTS `v_score1`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_score1` AS select `studentinfos`.`S_id` AS `S_id`,`studentinfos`.`S_name` AS `S_name`,`studentinfos`.`S_sex` AS `S_sex`,`studentinfos`.`S_major` AS `S_major`,`studentinfos`.`S_birth` AS `S_birth` from `studentinfos` where (`studentinfos`.`S_major` = '计算机') ;
-- ----------------------------
-- View structure for `v_score2`
-- ----------------------------
DROP VIEW IF EXISTS `v_score2`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_score2` AS select `studentinfos`.`S_major` AS `'专业'`,avg(`studentcourseinfos`.`Sc_grade`) AS `'平均成绩'` from (`studentinfos` join `studentcourseinfos`) (`studentinfos`.`S_id` = `studentcourseinfos`.`S_id`) group by `studentinfos`.`S_major` ;
mysql> select S_id,S_name,S_major,S_sex,S_birth
-> from StudentInfos
-> where S_id in(
-> select S_id
-> from StudentCourseInfos
-> group by S_id
-> having C_id !=(
-> select C_id
-> from CourseInfos
-> where C_name='C语言'));
1054 - Unknown column 'C_id' in 'having clause'
mysql>
直接在查询结果后where 筛选不就好了么，使用having也是要聚合函数 ，比如这种
SELECT S_id FROM sc GROUP BY S_id HAVING max( C_id)!=1 而不是直接 SELECT S_id FROM sc GROUP BY S_id HAVING C_id !=1
- 这篇博客: MySQL 基本操作中的 为学生表得c_id字段添加外键约束 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
alter table students add foreign key(c_id) references classes(id);