/* 題目請看圖片
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 */;
uj5u.com熱心網友回復：
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
索引自己建
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/52759.html
標籤：MySQL
上一篇：mysql安裝的時候無法初始化