新手mySQL查询不会了连思路都没咋办？

Dusdii@126.com 2019-08-07 09:58:07

/* 题目请看图片 SQLyog 企业版 - MySQL GUI v8.14 MySQL - 5.0.22-community-nt : Database - tedu ********************************************************************* */ /*!40101 SET NAMES utf8 */; /*!40101 SET SQL_MODE=''*/; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; CREATE DATABASE /*!32312 IF NOT EXISTS*/`tedu` /*!40100 DEFAULT CHARACTER SET utf8 */; USE `tedu`; /*Table structure for table `class` */ DROP TABLE IF EXISTS `class`; CREATE TABLE `class` ( `cla_id` int(11) NOT NULL, `cla_name` varchar(20) NOT NULL, `cla_hold` int(11) NOT NULL, PRIMARY KEY (`cla_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `class` */ LOCK TABLES `class` WRITE; insert into `class`(`cla_id`,`cla_name`,`cla_hold`) values (1,'软件1班',5),(2,'软件2班',10),(3,'软件3班',10),(4,'计网1班',5),(5,'计网2班',10),(6,'动漫1班',5),(7,'动漫2班',10); UNLOCK TABLES; /*Table structure for table `exam` */ DROP TABLE IF EXISTS `exam`; CREATE TABLE `exam` ( `exam_id` int(11) NOT NULL auto_increment, `exam_name` varchar(30) default NULL, `exam_time` datetime default NULL, `exam_class_id` int(11) NOT NULL, PRIMARY KEY (`exam_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `exam` */ LOCK TABLES `exam` WRITE; insert into `exam`(`exam_id`,`exam_name`,`exam_time`,`exam_class_id`) values (18,'软件班第一次考试1','2018-09-10 00:00:00',1),(19,'软件班第一次考试2','2018-09-10 00:00:00',2),(20,'软件班第一次考试3','2018-09-10 00:00:00',3),(21,'计网班第一次考试1','2018-09-11 00:00:00',4),(22,'计网班第一次考试2','2018-09-11 00:00:00',5),(23,'动漫班第一次考试1','2018-09-12 00:00:00',6),(24,'动漫班第一次考试2','2018-09-12 00:00:00',7),(25,'软件班第二次考试1','2018-10-10 00:00:00',1),(26,'软件班第二次考试2','2018-10-10 00:00:00',2),(27,'软件班第二次考试3','2018-10-10 00:00:00',3),(28,'计网班第二次考试1','2018-10-10 00:00:00',4),(29,'计网班第二次考试2','2018-10-10 00:00:00',5),(30,'动漫班第二次考试1','2018-10-10 00:00:00',6),(31,'动漫班第二次考试2','2018-10-10 00:00:00',7),(32,'软件班第三次考试1','2019-01-10 00:00:00',1),(33,'软件班第三次考试2','2019-01-10 00:00:00',2),(34,'软件班第三次考试3','2019-01-10 00:00:00',3); UNLOCK TABLES; /*Table structure for table `score` */ DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `score_id` int(11) NOT NULL auto_increment, `score_exam_id` int(11) NOT NULL, `score_stu_id` int(11) NOT NULL, `score_value` int(11) NOT NULL, PRIMARY KEY (`score_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `score` */ LOCK TABLES `score` WRITE; insert into `score`(`score_id`,`score_exam_id`,`score_stu_id`,`score_value`) values (297,18,1,85),(298,32,1,91),(299,19,2,88),(300,26,2,76),(301,33,2,68),(302,27,3,63),(303,21,4,66),(304,28,4,99),(305,30,6,97),(306,18,7,64),(307,25,7,63),(308,20,8,69),(309,27,8,58),(310,33,9,54),(311,28,10,74),(312,29,11,85),(313,23,12,52),(314,30,12,79),(315,18,13,73),(316,25,13,63),(317,26,14,79),(318,19,15,40),(319,26,15,73),(320,33,15,45),(321,20,16,40),(322,34,16,47),(323,21,17,75),(324,31,18,63),(325,23,19,56),(326,30,19,83),(327,29,20,79),(328,22,21,68),(329,29,21,85),(330,19,22,63),(331,26,22,80),(332,33,22,89),(333,20,23,96),(334,27,23,49),(335,34,23,99),(336,19,24,55),(337,20,25,55),(338,27,25,61),(339,34,25,86),(340,22,26,98),(341,29,26,51),(342,23,28,89),(343,30,28,86),(344,31,29,91),(345,23,30,49),(346,30,30,58),(347,28,31,96),(348,19,32,42),(349,26,32,75),(350,33,32,56),(351,19,33,61),(352,26,33,92),(353,33,33,52),(354,19,34,47),(355,27,36,79),(356,20,37,43),(357,34,37,84),(358,26,38,76),(359,20,39,49),(360,24,40,65),(361,31,40,42),(362,22,41,58),(363,29,41,68); UNLOCK TABLES; /*Table structure for table `student` */ DROP TABLE IF EXISTS `student`; CREATE TABLE `student` ( `stu_id` int(11) NOT NULL auto_increment, `stu_name` varchar(30) NOT NULL, `stu_age` int(11) default NULL, `stu_class_id` int(11) default NULL, PRIMARY KEY (`stu_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; /*Data for the table `student` */ LOCK TABLES `student` WRITE; insert into `student`(`stu_id`,`stu_name`,`stu_age`,`stu_class_id`) values (1,'刁英耀',22,1),(2,'白雪风',22,2),(3,'连华荣',21,3),(4,'范敏学',23,4),(5,'俞英才',25,5),(6,'印季萌',26,6),(7,'侯雅珺',22,1),(8,'穆光赫',23,3),(9,'匡良哲',20,2),(10,'贲向明',21,4),(11,'范奇致',30,5),(12,'常雪风',23,6),(13,'张华茂',19,1),(14,'宗俊才',33,2),(15,'张修文',32,2),(16,'张英韶',27,3),(17,'张永逸',20,4),(18,'李子昂',28,7),(19,'李勇锐',19,6),(20,'李鑫磊',30,5),(21,'李博容',31,5),(22,'李鑫莉',26,2),(23,'李飞捷',23,3),(24,'李浩旷',23,2),(25,'赵宏扬',22,3),(26,'赵英华',21,5),(27,'赵良骏',20,7),(28,'赵乌',22,6),(29,'马西',25,7),(30,'凌平',26,6),(31,'滕宁',25,4),(32,'庄水',27,2),(33,'从伟',20,2),(34,'劳阳',23,2),(35,'隗士',30,1),(36,'秋宁',31,3),(37,'单山',32,3),(38,'乌大',18,2),(39,'袁元',19,3),(40,'臧水',21,7),(41,'毛敖',23,5); UNLOCK TABLES; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

...全文

157 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

Dleno 2019-08-09

打赏
举报



SELECT

	cs.cla_name,s.*,cs.en,c.sn

FROM

	`student` s

LEFT JOIN (

SELECT cs.cla_id,cs.cla_name,count(e.exam_id) en from class cs

LEFT JOIN exam e on e.exam_class_id=cs.cla_id

GROUP BY cs.cla_id

) cs on cs.cla_id=s.stu_class_id

LEFT JOIN (

SELECT c.score_stu_id,count(c.score_id) sn FROM score c GROUP BY c.score_stu_id

) c on c.score_stu_id=s.stu_id

where cs.en-IFNULL(c.sn,0)>=2

索引自己建