数据库案例——查询选择了大学语文而没有选择线性代数的学生_navicat查询选了这个课没选另一个课的学生-优快云博客

本文链接：https://blog.youkuaiyun.com/ttyule1/article/details/107863459

本文介绍了一种SQL查询方法，用于找出选择了大学语文但未选线性代数的学生信息，通过对数据库中学生选课记录的分析，实现了精确筛选。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

题目：
查询选择了大学语文而没有选择线性代数的学生
以下是本题的数据库文件，导入即可使用。

/*
Navicat MySQL Data Transfer

Source Server         : db
Source Server Version : 50714
Source Host           : localhost:3306
Source Database       : db_choose

Target Server Type    : MYSQL
Target Server Version : 50714
File Encoding         : 65001

Date: 2020-08-07 15:32:53
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `tbl_choose`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_choose`;
CREATE TABLE `tbl_choose` (
  `choose_no` int(11) NOT NULL AUTO_INCREMENT,
  `stu_no` int(11) NOT NULL,
  `course_no` int(11) NOT NULL,
  PRIMARY KEY (`choose_no`),
  KEY `stu_no` (`stu_no`),
  KEY `course_no` (`course_no`),
  CONSTRAINT `tbl_choose_ibfk_1` FOREIGN KEY (`stu_no`) REFERENCES `tbl_stu` (`stu_no`),
  CONSTRAINT `tbl_choose_ibfk_2` FOREIGN KEY (`course_no`) REFERENCES `tbl_course` (`course_no`)
) ENGINE=InnoDB AUTO_INCREMENT=27 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_choose
-- ----------------------------
INSERT INTO `tbl_choose` VALUES ('1', '1', '1');
INSERT INTO `tbl_choose` VALUES ('2', '1', '2');
INSERT INTO `tbl_choose` VALUES ('3', '1', '3');
INSERT INTO `tbl_choose` VALUES ('4', '1', '4');
INSERT INTO `tbl_choose` VALUES ('5', '2', '1');
INSERT INTO `tbl_choose` VALUES ('6', '2', '2');
INSERT INTO `tbl_choose` VALUES ('7', '2', '3');
INSERT INTO `tbl_choose` VALUES ('8', '3', '1');
INSERT INTO `tbl_choose` VALUES ('9', '4', '3');
INSERT INTO `tbl_choose` VALUES ('10', '4', '4');
INSERT INTO `tbl_choose` VALUES ('11', '5', '1');
INSERT INTO `tbl_choose` VALUES ('12', '5', '3');
INSERT INTO `tbl_choose` VALUES ('13', '5', '4');
INSERT INTO `tbl_choose` VALUES ('14', '6', '1');
INSERT INTO `tbl_choose` VALUES ('15', '6', '2');
INSERT INTO `tbl_choose` VALUES ('16', '6', '3');
INSERT INTO `tbl_choose` VALUES ('17', '6', '4');
INSERT INTO `tbl_choose` VALUES ('18', '7', '2');
INSERT INTO `tbl_choose` VALUES ('19', '7', '3');
INSERT INTO `tbl_choose` VALUES ('20', '7', '4');
INSERT INTO `tbl_choose` VALUES ('21', '8', '2');
INSERT INTO `tbl_choose` VALUES ('22', '8', '3');
INSERT INTO `tbl_choose` VALUES ('23', '9', '4');
INSERT INTO `tbl_choose` VALUES ('24', '9', '1');
INSERT INTO `tbl_choose` VALUES ('25', '9', '3');
INSERT INTO `tbl_choose` VALUES ('26', '10', '1');

-- ----------------------------
-- Table structure for `tbl_class`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_class`;
CREATE TABLE `tbl_class` (
  `class_no` int(11) NOT NULL AUTO_INCREMENT,
  `class_name` varchar(255) NOT NULL,
  PRIMARY KEY (`class_no`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_class
-- ----------------------------
INSERT INTO `tbl_class` VALUES ('1', '1801');
INSERT INTO `tbl_class` VALUES ('2', '1802');
INSERT INTO `tbl_class` VALUES ('3', '1803');

-- ----------------------------
-- Table structure for `tbl_course`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_course`;
CREATE TABLE `tbl_course` (
  `course_no` int(11) NOT NULL AUTO_INCREMENT,
  `course_name` varchar(255) NOT NULL,
  PRIMARY KEY (`course_no`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_course
-- ----------------------------
INSERT INTO `tbl_course` VALUES ('1', '高等数学');
INSERT INTO `tbl_course` VALUES ('2', '大学语文');
INSERT INTO `tbl_course` VALUES ('3', '线性代数');
INSERT INTO `tbl_course` VALUES ('4', '大学英语');

-- ----------------------------
-- Table structure for `tbl_sp`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_sp`;
CREATE TABLE `tbl_sp` (
  `sp_no` int(11) NOT NULL AUTO_INCREMENT,
  `sp_name` varchar(255) NOT NULL,
  PRIMARY KEY (`sp_no`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_sp
-- ----------------------------
INSERT INTO `tbl_sp` VALUES ('1', '计算机专业');
INSERT INTO `tbl_sp` VALUES ('2', '电气专业');
INSERT INTO `tbl_sp` VALUES ('3', '英语专业');
INSERT INTO `tbl_sp` VALUES ('4', '物理专业');

-- ----------------------------
-- Table structure for `tbl_stu`
-- ----------------------------
DROP TABLE IF EXISTS `tbl_stu`;
CREATE TABLE `tbl_stu` (
  `stu_no` int(11) NOT NULL AUTO_INCREMENT,
  `stu_name` varchar(255) NOT NULL,
  `stu_sex` varchar(10) NOT NULL,
  `stu_birthday` date NOT NULL,
  `stu_age` int(11) NOT NULL,
  `stu_score` float NOT NULL,
  `stu_address` varchar(255) NOT NULL,
  `stu_class` varchar(255) NOT NULL,
  `sp_no` int(11) DEFAULT NULL,
  `stu_other` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`stu_no`),
  KEY `sp_no` (`sp_no`),
  CONSTRAINT `tbl_stu_ibfk_1` FOREIGN KEY (`sp_no`) REFERENCES `tbl_sp` (`sp_no`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of tbl_stu
-- ----------------------------
INSERT INTO `tbl_stu` VALUES ('1', '高明', '男', '2004-03-13', '19', '90', '山东省泰安市', '1', '1', null);
INSERT INTO `tbl_stu` VALUES ('2', '小明', '男', '1999-10-28', '20', '85', '陕西省西安市', '1', '2', null);
INSERT INTO `tbl_stu` VALUES ('3', '张三', '女', '1998-09-08', '17', '78', '陕西省西安市', '2', '1', null);
INSERT INTO `tbl_stu` VALUES ('4', '李四', '男', '1997-02-09', '19', '89', '山东省泰安市', '2', '2', null);
INSERT INTO `tbl_stu` VALUES ('5', '王五', '女', '1998-10-10', '20', '99', '山东省济南市', '3', '3', null);
INSERT INTO `tbl_stu` VALUES ('6', '小李', '男', '2001-10-09', '18', '88', '山东省济南市', '1', '1', null);
INSERT INTO `tbl_stu` VALUES ('7', '小张', '女', '2000-10-02', '19', '98', '陕西省西安市', '2', '2', null);
INSERT INTO `tbl_stu` VALUES ('8', '小吴', '女', '2000-12-10', '20', '78', '陕西省宝鸡市', '1', null, null);
INSERT INTO `tbl_stu` VALUES ('9', '小贾', '男', '2000-12-13', '20', '100', '山东省泰安市·', '1', null, null);
INSERT INTO `tbl_stu` VALUES ('10', '小强', '男', '1998-04-10', '23', '90', '陕西省宝鸡市', '3', '1', null);
INSERT INTO `tbl_stu` VALUES ('11', '小刘', '女', '1997-10-10', '24', '87', '陕西省西安市', '3', '2', null);

本人答案：

SELECT
	*
FROM
	tbl_stu
WHERE
	stu_no
IN
	(
		SELECT
			stu_no
		FROM
			(SELECT
				stu_no,
				max(CASE WHEN course_no = '2' THEN course_no END) AS '大学语文编号' ,
				max(CASE WHEN course_no = '3' THEN course_no END) AS '线性代数编号' 
			FROM
				tbl_choose
			GROUP BY
				stu_no) AS newtal
			WHERE
				大学语文编号=(
					SELECT
						course_no
					FROM
						tbl_course
					WHERE
						course_name = '大学语文'
			)
		AND
			线性代数编号=(
					SELECT
						course_no
					FROM
						tbl_course
					WHERE
						course_name = '线性代数'
			)
)