SQl：找出每个班级里身高最高的人，并按班级排序

女爷i 2022-10-22 12:51 202阅读 0赞

**SQl：找出每个班级里身高最高的人，并按班级排序**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MzA2OTky_size_16_color_FFFFFF_t_70]

**数据库脚本**

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
    /*!40101 SET NAMES utf8 */;
    /*!50503 SET NAMES utf8mb4 */;
    /*!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' */;
    
    -- 导出  表 test.tbl_middle_class 结构
    CREATE TABLE IF NOT EXISTS `tbl_middle_class` (
      `id` int(11) NOT NULL,
      `name` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
    
    -- 正在导出表  test.tbl_middle_class 的数据：~3 rows (大约)
    DELETE FROM `tbl_middle_class`;
    /*!40000 ALTER TABLE `tbl_middle_class` DISABLE KEYS */;
    INSERT INTO `tbl_middle_class` (`id`, `name`) VALUES
    	(1, 'Grade1'),
    	(2, 'Grade2'),
    	(3, 'Grade3');
    /*!40000 ALTER TABLE `tbl_middle_class` ENABLE KEYS */;
    
    -- 导出  表 test.tbl_middle_score 结构
    CREATE TABLE IF NOT EXISTS `tbl_middle_score` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `stu_id` int(11) NOT NULL,
      `course_name` varchar(50) NOT NULL,
      `score` int(11) NOT NULL,
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=latin1;
    
    -- 正在导出表  test.tbl_middle_score 的数据：~40 rows (大约)
    DELETE FROM `tbl_middle_score`;
    /*!40000 ALTER TABLE `tbl_middle_score` DISABLE KEYS */;
    INSERT INTO `tbl_middle_score` (`id`, `stu_id`, `course_name`, `score`) VALUES
    	(1, 1, 'Chinese', 80),
    	(2, 1, 'Math', 66),
    	(3, 1, 'English', 91),
    	(4, 1, 'Sport', 88),
    	(5, 2, 'Chinese', 99),
    	(6, 2, 'Math', 100),
    	(7, 2, 'English', 88),
    	(8, 2, 'Sport', 81),
    	(9, 3, 'Chinese', 81),
    	(10, 3, 'Math', 60),
    	(11, 3, 'English', 100),
    	(12, 3, 'Sport', 90),
    	(13, 4, 'Chinese', 77),
    	(14, 4, 'Math', 85),
    	(15, 4, 'English', 96),
    	(16, 4, 'Sport', 66),
    	(17, 5, 'Chinese', 55),
    	(18, 5, 'Math', 85),
    	(19, 5, 'English', 45),
    	(20, 5, 'Sport', 100),
    	(21, 6, 'Chinese', 86),
    	(22, 6, 'Math', 100),
    	(23, 6, 'English', 56),
    	(24, 6, 'Sport', 75),
    	(25, 7, 'Chinese', 100),
    	(26, 7, 'Math', 99),
    	(27, 7, 'English', 85),
    	(28, 7, 'Sport', 78),
    	(29, 8, 'Chinese', 86),
    	(30, 8, 'Math', 100),
    	(31, 8, 'English', 100),
    	(32, 8, 'Sport', 100),
    	(33, 9, 'Chinese', 86),
    	(34, 9, 'Math', 100),
    	(35, 9, 'English', 87),
    	(36, 9, 'Sport', 100),
    	(37, 10, 'Chinese', 79),
    	(38, 10, 'Math', 56),
    	(39, 10, 'English', 85),
    	(40, 10, 'Sport', 99);
    /*!40000 ALTER TABLE `tbl_middle_score` ENABLE KEYS */;
    
    -- 导出  表 test.tbl_middle_student 结构
    CREATE TABLE IF NOT EXISTS `tbl_middle_student` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) NOT NULL,
      `age` int(11) NOT NULL,
      `grade` varchar(50) NOT NULL,
      `gender` varchar(50) NOT NULL,
      `height` int(11) DEFAULT NULL COMMENT '身高',
      `classid` int(11) DEFAULT NULL COMMENT '班级ID',
      PRIMARY KEY (`id`) USING BTREE
    ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
    
    -- 正在导出表  test.tbl_middle_student 的数据：~10 rows (大约)
    DELETE FROM `tbl_middle_student`;
    /*!40000 ALTER TABLE `tbl_middle_student` DISABLE KEYS */;
    INSERT INTO `tbl_middle_student` (`id`, `name`, `age`, `grade`, `gender`, `height`, `classid`) VALUES
    	(1, 'jack', 13, '3', 'male', 160, 1),
    	(2, 'kate', 13, '3', 'female', 165, 1),
    	(3, 'candy', 13, '3', 'female', 155, 1),
    	(4, 'tom', 13, '3', 'male', 175, 2),
    	(5, 'bob', 13, '3', 'male', 167, 2),
    	(6, 'jerry', 13, '3', 'male', 166, 2),
    	(7, 'mary', 13, '3', 'female', 167, 3),
    	(8, 'amanda', 13, '3', 'female', 170, 3),
    	(9, 'lee', 13, '3', 'male', 175, 3),
    	(10, 'mike', 13, '3', 'male', 178, 2);
    /*!40000 ALTER TABLE `tbl_middle_student` ENABLE KEYS */;
    
    /*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
    /*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
    /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

**首先查出每个班级最高的人**

select  classid,max(height) from  tbl_middle_student  GROUP BY classid

**这里是执行结果**

![在这里插入图片描述][2021031712341396.png]

**从学生表查出以下sql**

select s.* from tbl_middle_student s,(
     
     SELECT classid,MAX(height) as height from tbl_middle_student GROUP BY classid
     
    ) b where s.classid = b.classid and s.height = b.height

![在这里插入图片描述][2021031712372816.png]  
\*\*和class表进联合查询 \*\*

select c.name as classname,d.name,d.height from tbl_middle_class c,(
     
     select s.* from tbl_middle_student s,(SELECT classid,MAX(height) as height from    tbl_middle_student GROUP BY classid
    
    ) b where s.classid = b.classid and s.height = b.height) d where c.id=d.classid ORDER BY classname

**这里需要注意ORDER BY 和 GROUP BY的区别：**

1，order by 从英文里理解就是行的排序方式，默认的为升序。 order by 后面必须列出排序的字段名，可以是多个字段名。

2，group by 从英文里理解就是分组。必须有“聚合函数”来配合才能使用，使用时至少需要一个分组标志字段。

3，在使用group by的语句中，只能select用于分类的列（表达式），或聚合函数。  
where条件用于group by之前，having用于group by 之后对结果进行筛选。

**mysql 中order by 与group by的顺序 是：**

**select  
from  
where  
group by  
order by**

注意：group by 比order by先执行，order by不会对group by 内部进行排序，如果group by后只有一条记录，那么order by 将无效。要查出group by中最大的或最小的某一字段使用 max或min函数。

如下sql单独用到group by的时候

select  classid,max(height) from  tbl_middle_student  GROUP BY classid

![在这里插入图片描述][2021031712441284.png]

单独用到order by的时候

select  classid,max(height) from  tbl_middle_student  ORDER BY classid desc

![在这里插入图片描述][20210317124421941.png]

一起使用的时候

select  classid,max(height) from  tbl_middle_student  GROUP BY classid ORDER BY classid desc

![在这里插入图片描述][20210317124501708.png]

当然如果order by 没有标注倒叙排列的话是跟单独使用group by的作用无异的

select  classid,max(height) from  tbl_middle_student  GROUP BY classid ORDER BY classid

![在这里插入图片描述][20210317124652246.png]

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MzA2OTky_size_16_color_FFFFFF_t_70 1]

#LEFT JOIN 
    SELECT DISTINCT(name) from tbl_middle_score sc 
    LEFT JOIN tbl_middle_student stu 
    on  sc.stu_id = stu.id where score =100 ORDER BY CONVERT(name USING GBK) ASC;

#in查询
    SELECT  name from  tbl_middle_student where id in
    (SELECT stu_id from tbl_middle_score where score =100  ) ORDER BY  CONVERT(name  USING GBK) ASC ;

#INNER JOIN 
    SELECT DISTINCT(stu.name) FROM tbl_middle_student stu 
    INNER JOIN tbl_middle_score  sc 
    ON stu.id = sc.stu_id where sc.score =100  ORDER BY  CONVERT(stu.name  USING GBK) ASC ;

小记， 若有不足之处还望大家支出！

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MzA2OTky_size_16_color_FFFFFF_t_70]: /images/20221022/a104637ff71545d9a4be5c8c8e0895b1.png
[2021031712341396.png]: /images/20221022/4c013f2fb7794522bbed6ad226cd6238.png
[2021031712372816.png]: /images/20221022/df1c36cc6d3c42f5b7396eac1fdf6489.png
[2021031712441284.png]: /images/20221022/c12a6aeb06b742d684a84dd6e650ac48.png
[20210317124421941.png]: /images/20221022/3250a8f41f214bbd85883a6749c86008.png
[20210317124501708.png]: /images/20221022/881a400bd50a41769f8dcf575cebcfa3.png
[20210317124652246.png]: /images/20221022/0da09137f7584a0ba75b972b45e72489.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM5MzA2OTky_size_16_color_FFFFFF_t_70 1]: /images/20221022/3fee40ce5a5842d08f4bfaf2d076a6b4.png