/* 题目请看图片
SQLyog 企业版 - MySQL GUI v8.14 
MySQL - 5.0.22-community-nt : Database - tedu
*********************************************************************
*/
/*!40101 SET NAMES utf8 */;/*!40101 SET SQL_MODE=''*/;/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@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 [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40111 SET [email protected]_SQL_NOTES */;

解决方案 »

  1.   


    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索引自己建