查询问题

想写个报道人数统计的语句
select b.classid, b.classname, c.departmentname, d.collegename, count(*) as regstunum, (select count(*) from student where classid=a.classid ) as allstunum  from student as a ,class as b , department as c ,college as d where a.classid=b.classid and b.departmentid=c.departmentid and c.collegeid=d.collegeid group by a.classid这样查出来的结果只有报道过的班级才显示
我想把所有班级都打印出来 ，不管报道与否
mysql> select * from student;
+----------+----------+------+------+----------+-------------+-------+---------+------------+----------+-------+---------+--------+------+
| id       | name     | sex  | age  | address  | phone       | bedid | bedcost | registered | spending | spent | classid | userid | date |
+----------+----------+------+------+----------+-------------+-------+---------+------------+----------+-------+---------+--------+------+
| 91010101 | 丁乙甲   | 男   |   21 | 通化     | 13900000000 |   111 |    1111 | Y          |     2000 |  3111 |   10101 |   NULL | 2009 |
| 91010102 | 索春龙   | 男   |   22 | 安徽     | 13910000000 |     0 |       0 | N          |     2000 |     0 |   10101 |   NULL | 2009 |
| 91010103 | 刘莉莉   | 女   |   22 | 白城     | 13920000000 |     0 |       0 | N          |     2000 |     0 |   10101 |   NULL | 2009 |
| 91010104 | 隋鑫     | 女   |   22 | 松原     | 13930000000 |     0 |       0 | N          |     2000 |     0 |   10101 |   NULL | 2009 |
| 91010105 | 长命     | 男   |   22 | 地府     | 12222222222 |     0 |       0 | N          |     2000 |     0 |   10101 |   NULL | 2009 |
| 91010201 | 河江山   | 男   |   20 | 陕西     | 13800000000 |     0 |       0 | N          |     2000 |     0 |   10102 |   NULL | 2009 |
| 91010202 | 祝立业   | 男   |   20 | 长春     | 13800000000 |     0 |       0 | N          |     2000 |     0 |   10102 |   NULL | 2009 |
| 91010203 | 姚桂娇   | 女   |   23 | 长春     | 13800000000 |     0 |       0 | N          |     2000 |     0 |   10102 |   NULL | 2009 |
| 91010204 | 张冬梅   | 女   |   20 | 农安     | 13800000000 |     0 |       0 | N          |     2000 |     0 |   10102 |   NULL | 2009 |
| 91020101 | 蒋旭     | 男   |   24 | 长江路   | 13700000000 |     0 |       0 | N          |     2500 |     0 |   10201 |   NULL | 2009 |
| 91020102 | 邱介臣   | 男   |   24 | 延吉     | 13700000000 |     0 |       0 | N          |     2500 |     0 |   10201 |   NULL | 2009 |
| 91020103 | 王晓燕   | 女   |   24 | 河南     | 13700000000 |     0 |       0 | N          |     2500 |     0 |   10201 |   NULL | 2009 |
| 91020104 | 夏丽佳   | 女   |   23 | 湖南     | 13700060000 |     0 |       0 | N          |     2500 |     0 |   10201 |   NULL | 2009 |
| 91020201 | 熊建业   | 男   |   25 | 三盛玉   | 13730060000 |     0 |       0 | N          |     2500 |     0 |   10202 |   NULL | 2009 |
| 91020202 | 丛俊江   | 男   |   25 | 杨树林   | 13730060000 |     0 |       0 | N          |     2500 |     0 |   10202 |   NULL | 2009 |
mysql> select * from college;
+-----------+------------------+
| collegeid | collegename      |
+-----------+------------------+
|         1 | 机械工程学院     |
|         2 | 电子信息工程学院 |
|         3 | 计算机学院       |
+-----------+------------------+
3 rows in set (0.00 sec)mysql> select * from department;
+--------------+----------------+-----------+-------+
| departmentid | departmentname | collegeid | money |
+--------------+----------------+-----------+-------+
|          101 | 工业工程系     |         1 |  2000 |
|          102 | 工业设计系     |         1 |  2500 |
|          103 | 机械自动化系   |         1 |  3000 |
|          201 | 通信工程系     |         2 |  3500 |
|          202 | 电气工程系     |         2 |  4000 |
|          203 | 自动化系       |         2 |  4500 |
|          204 | 测控技术系     |         2 |  5000 |
|          301 | 计算机技术系   |         3 |  5500 |
|          302 | 网络工程系     |         3 |  6000 |
+--------------+----------------+-----------+-------+mysql> select * from class;
+---------+-----------------+--------------+
| classid | classname       | departmentid |
+---------+-----------------+--------------+
|   10101 | 工业工程系1班   |          101 |
|   10102 | 工业工程系2班   |          101 |
|   10201 | 工业设计系1班   |          102 |
|   10202 | 工业设计系2班   |          102 |
|   10203 | 工业设计系3班   |          102 |
|   10301 | 机械自动化系1班 |          103 |
|   10302 | 机械自动化系2班 |          103 |
|   10303 | 机械自动化系3班 |          103 |
|   20101 | 通信工程系1班   |          201 |
|   20102 | 通信工程系2班   |          201 |
|   20201 | 电气工程系1班   |          202 |
|   20202 | 电气工程系2班   |          202 |
|   20301 | 自动化系1班     |          203 |
|   20302 | 自动化系2班     |          203 |
|   20401 | 测控技术系1班   |          204 |
|   20402 | 测控技术系2班   |          204 |
|   30101 | 计算机技术系1班 |          301 |
|   30102 | 计算机技术系2班 |          301 |
|   30201 | 网络工程系1班   |          302 |
|   30202 | 网络工程系2班   |          302 |
+---------+-----------------+--------------+
20 rows in set (0.00 sec)

解决方案 »

  1.   

    表结构
    SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for student
    -- ----------------------------
    DROP TABLE IF EXISTS `student`;
    CREATE TABLE `student` (
      `id` int(50) NOT NULL,
      `name` varchar(50) NOT NULL,
      `sex` varchar(50) DEFAULT NULL,
      `age` int(11) DEFAULT NULL,
      `address` varchar(50) DEFAULT NULL,
      `phone` varchar(50) DEFAULT NULL,
      `bedid` int(50) DEFAULT '0',
      `bedcost` int(11) DEFAULT '0',
      `registered` varchar(10) DEFAULT NULL,
      `spending` int(11) DEFAULT NULL,
      `spent` int(11) DEFAULT '0',
      `classid` int(11) DEFAULT NULL,
      `userid` int(11) DEFAULT NULL,
      `date` varchar(11) DEFAULT NULL,
      PRIMARY KEY (`id`),
      KEY `bedid` (`bedid`),
      KEY `userid` (`userid`),
      KEY `classid` (`classid`),
      CONSTRAINT `bedid` FOREIGN KEY (`bedid`) REFERENCES `bed` (`bedid`),
      CONSTRAINT `classid` FOREIGN KEY (`classid`) REFERENCES `class` (`classid`),
      CONSTRAINT `userid` FOREIGN KEY (`userid`) REFERENCES `user` (`userid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312;-- ----------------------------
    -- Records 
    -- ----------------------------
    INSERT INTO `student` VALUES ('91010101', '丁乙甲', '男', '21', '通化', '13900000000', '111', '1111', 'Y', '2000', '3111', '10101', null, '2009');
    INSERT INTO `student` VALUES ('91010102', '索春龙', '男', '22', '安徽', '13910000000', '0', '0', 'N', '2000', '0', '10101', null, '2009');
    INSERT INTO `student` VALUES ('91010103', '刘莉莉', '女', '22', '白城', '13920000000', '0', '0', 'N', '2000', '0', '10101', null, '2009');
    INSERT INTO `student` VALUES ('91010104', '隋鑫', '女', '22', '松原', '13930000000', '0', '0', 'N', '2000', '0', '10101', null, '2009');
    INSERT INTO `student` VALUES ('91010105', '长命', '男', '22', '地府', '12222222222', '0', '0', 'N', '2000', '0', '10101', null, '2009');
    INSERT INTO `student` VALUES ('91010201', '河江山', '男', '20', '陕西', '13800000000', '0', '0', 'N', '2000', '0', '10102', null, '2009');
    INSERT INTO `student` VALUES ('91010202', '祝立业', '男', '20', '长春', '13800000000', '0', '0', 'N', '2000', '0', '10102', null, '2009');
    INSERT INTO `student` VALUES ('91010203', '姚桂娇', '女', '23', '长春', '13800000000', '0', '0', 'N', '2000', '0', '10102', null, '2009');
    INSERT INTO `student` VALUES ('91010204', '张冬梅', '女', '20', '农安', '13800000000', '0', '0', 'N', '2000', '0', '10102', null, '2009');
    INSERT INTO `student` VALUES ('91020101', '蒋旭', '男', '24', '长江路', '13700000000', '0', '0', 'N', '2500', '0', '10201', null, '2009');
    INSERT INTO `student` VALUES ('91020102', '邱介臣', '男', '24', '延吉', '13700000000', '0', '0', 'N', '2500', '0', '10201', null, '2009');
    INSERT INTO `student` VALUES ('91020103', '王晓燕', '女', '24', '河南', '13700000000', '0', '0', 'N', '2500', '0', '10201', null, '2009');
    INSERT INTO `student` VALUES ('91020104', '夏丽佳', '女', '23', '湖南', '13700060000', '0', '0', 'N', '2500', '0', '10201', null, '2009');
    INSERT INTO `student` VALUES ('91020201', '熊建业', '男', '25', '三盛玉', '13730060000', '0', '0', 'N', '2500', '0', '10202', null, '2009');
    INSERT INTO `student` VALUES ('91020202', '丛俊江', '男', '25', '杨树林', '13730060000', '0', '0', 'N', '2500', '0', '10202', null, '2009');
    INSERT INTO `student` VALUES ('91020203', '周静燕', '女', '21', '杨树林', '13730060000', '0', '0', 'N', '2500', '0', '10202', null, '2009');
    INSERT INTO `student` VALUES ('91020204', '黄爽', '女', '20', '杨树林', '13730060000', '0', '0', 'N', '2500', '0', '10202', null, '2009');
    INSERT INTO `student` VALUES ('91020301', '魏国兴', '男', '25', '新兴', '13730060000', '0', '0', 'N', '2500', '0', '10203', null, '2009');
    INSERT INTO `student` VALUES ('91020302', '蒋洪涛', '男', '23', '新兴', '13730060000', '0', '0', 'N', '2500', '0', '10203', null, '2009');
    INSERT INTO `student` VALUES ('91020303', '倪春红', '女', '22', '新兴', '13730060000', '0', '0', 'N', '2500', '0', '10203', null, '2009');
    INSERT INTO `student` VALUES ('91030101', '张文天', '男', '22', '靠山', '13730060000', '0', '0', 'N', '3000', '0', '10301', null, '2009');
    INSERT INTO `student` VALUES ('91030102', '姜洋', '男', '22', '杨树林', '13730060000', '0', '0', 'N', '3000', '0', '10301', null, '2009');
    INSERT INTO `student` VALUES ('91030103', '赵聪', '女', '22', '三盛玉', '13730060000', '0', '0', 'N', '3000', '0', '10301', null, '2009');
    INSERT INTO `student` VALUES ('91030201', '王飞', '男', '22', '永安', '13730060000', '0', '0', 'N', '3000', '0', '10302', null, '2009');SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for department
    -- ----------------------------
    DROP TABLE IF EXISTS `department`;
    CREATE TABLE `department` (
      `departmentid` int(11) NOT NULL,
      `departmentname` varchar(50) COLLATE gb2312_bin NOT NULL,
      `collegeid` int(11) NOT NULL,
      `money` int(11) DEFAULT NULL,
      PRIMARY KEY (`departmentid`),
      KEY `collegeid` (`collegeid`),
      CONSTRAINT `collegeid` FOREIGN KEY (`collegeid`) REFERENCES `college` (`collegeid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin;-- ----------------------------
    -- Records 
    -- ----------------------------
    INSERT INTO `department` VALUES ('101', '工业工程系', '1', '2000');
    INSERT INTO `department` VALUES ('102', '工业设计系', '1', '2500');
    INSERT INTO `department` VALUES ('103', '机械自动化系', '1', '3000');
    INSERT INTO `department` VALUES ('201', '通信工程系', '2', '3500');
    INSERT INTO `department` VALUES ('202', '电气工程系', '2', '4000');
    INSERT INTO `department` VALUES ('203', '自动化系', '2', '4500');
    INSERT INTO `department` VALUES ('204', '测控技术系', '2', '5000');
    INSERT INTO `department` VALUES ('301', '计算机技术系', '3', '5500');
    INSERT INTO `department` VALUES ('302', '网络工程系', '3', '6000');SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for college
    -- ----------------------------
    DROP TABLE IF EXISTS `college`;
    CREATE TABLE `college` (
      `collegeid` int(11) NOT NULL,
      `collegename` varchar(50) COLLATE gb2312_bin NOT NULL,
      PRIMARY KEY (`collegeid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin;-- ----------------------------
    -- Records 
    -- ----------------------------
    INSERT INTO `college` VALUES ('1', '机械工程学院');
    INSERT INTO `college` VALUES ('2', '电子信息工程学院');
    INSERT INTO `college` VALUES ('3', '计算机学院');SET FOREIGN_KEY_CHECKS=0;
    -- ----------------------------
    -- Table structure for class
    -- ----------------------------
    DROP TABLE IF EXISTS `class`;
    CREATE TABLE `class` (
      `classid` int(11) NOT NULL,
      `classname` varchar(50) COLLATE gb2312_bin NOT NULL,
      `departmentid` int(11) NOT NULL,
      PRIMARY KEY (`classid`),
      KEY `departmentid` (`departmentid`),
      CONSTRAINT `departmentid` FOREIGN KEY (`departmentid`) REFERENCES `department` (`departmentid`)
    ) ENGINE=InnoDB DEFAULT CHARSET=gb2312 COLLATE=gb2312_bin;-- ----------------------------
    -- Records 
    -- ----------------------------
    INSERT INTO `class` VALUES ('10101', '工业工程系1班 ', '101');
    INSERT INTO `class` VALUES ('10102', '工业工程系2班', '101');
    INSERT INTO `class` VALUES ('10201', '工业设计系1班', '102');
    INSERT INTO `class` VALUES ('10202', '工业设计系2班', '102');
    INSERT INTO `class` VALUES ('10203', '工业设计系3班', '102');
    INSERT INTO `class` VALUES ('10301', '机械自动化系1班', '103');
    INSERT INTO `class` VALUES ('10302', '机械自动化系2班', '103');
    INSERT INTO `class` VALUES ('10303', '机械自动化系3班', '103');
    INSERT INTO `class` VALUES ('20101', '通信工程系1班', '201');
    INSERT INTO `class` VALUES ('20102', '通信工程系2班', '201');
    INSERT INTO `class` VALUES ('20201', '电气工程系1班', '202');
    INSERT INTO `class` VALUES ('20202', '电气工程系2班', '202');
    INSERT INTO `class` VALUES ('20301', '自动化系1班', '203');
    INSERT INTO `class` VALUES ('20302', '自动化系2班', '203');
    INSERT INTO `class` VALUES ('20401', '测控技术系1班', '204');
    INSERT INTO `class` VALUES ('20402', '测控技术系2班', '204');
    INSERT INTO `class` VALUES ('30101', '计算机技术系1班', '301');
    INSERT INTO `class` VALUES ('30102', '计算机技术系2班', '301');
    INSERT INTO `class` VALUES ('30201', '网络工程系1班', '302');
    INSERT INTO `class` VALUES ('30202', '网络工程系2班', '302');
      

  2.   

    我写的语句的执行结果。+---------+----------------+----------------+--------------+-----------+-----------+
    | classid | classname      | departmentname | collegename  | regstunum | allstunum |
    +---------+----------------+----------------+--------------+-----------+-----------+
    |   10101 | 工业工程系1班  | 工业工程系     | 机械工程学院 |         1 |         5 |
    +---------+----------------+----------------+--------------+-----------+-----------+
    1 row in set (0.00 sec)我是只是想把所有班级都列出来
      

  3.   

    搞定了。自己发帖。自己答。分白仍了。哎。。select b.classid, b.classname, c.departmentname, d.collegename, (select count(*) from student where registered ='Y' and classid=a.classid)as regstunum, (select count(*) from student where classid=a.classid ) as allstunum  from student as a ,class as b , department as c ,college as d where a.classid=b.classid and b.departmentid=c.departmentid and c.collegeid=d.collegeid group by a.classid
      

  4.   

    效率上比较快的一种写法
    mysql> select c.classid,c.classname,d.departmentname,g.collegename,
        ->  sum(if(registered='Y',1,0)) as regstunum,count(*) as allstunum
        -> from class c inner join department d on c.departmentid=d.departmentid
        ->  inner join college g on d.collegeid=g.collegeid
        ->  inner join student s on c.classid=s.classid
        -> group by c.classid,c.classname,d.departmentname,g.collegename;
    +---------+-----------------+----------------+--------------+-----------+-----------+
    | classid | classname       | departmentname | collegename  | regstunum | allstunum |
    +---------+-----------------+----------------+--------------+-----------+-----------+
    |   10101 | 工业工程系1班   | 工业工程系     | 机械工程学院 |         1 |         5 |
    |   10102 | 工业工程系2班   | 工业工程系     | 机械工程学院 |         0 |         4 |
    |   10201 | 工业设计系1班   | 工业设计系     | 机械工程学院 |         0 |         4 |
    |   10202 | 工业设计系2班   | 工业设计系     | 机械工程学院 |         0 |         4 |
    |   10203 | 工业设计系3班   | 工业设计系     | 机械工程学院 |         0 |         3 |
    |   10301 | 机械自动化系1班 | 机械自动化系   | 机械工程学院 |         0 |         3 |
    |   10302 | 机械自动化系2班 | 机械自动化系   | 机械工程学院 |         0 |         1 |
    +---------+-----------------+----------------+--------------+-----------+-----------+
    7 rows in set (0.00 sec)