记录一下小小的sql：找出每个班级里身高最高的人，并按班级排序

-- 导出  表 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 */;

以上是sql语句方便测试：注意上面没有database,创建一个，执行上述语句即可

第一种比较麻烦，适合理解

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

其实就是几个sql拼在一起查出来的

思路：找出最高的人，然后从student表中找出这条记录，再与班级联合查询

1.找出最高的人

SELECT classid,MAX(height) as height from tbl_middle_student GROUP BY classid

//结果
1	165
2	178
3	175

2.从student表中找出这条记录

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

//结果
2	kate	13	3	female	165	1
9	lee	13	3	male	175	3
10	mike	13	3	male	178	2

3.与class表进行联合查询也就是结果语句

第二种：两种都思路差不多，第二种简洁一点

select c.name as classname,st.`name`,st.height from 
tbl_middle_student st,tbl_middle_class c,(SELECT classid, MAX(height) as height from tbl_middle_student GROUP BY classid) h  WHERE
 st.classid = c.id and st.height = h.height and st.classid = h.classid ORDER BY classname 

小小记录一下。。。。。

补充：笛卡尔积的应用

//分组以后取第2条
Select * from tbl_middle_student a where 1=(select count(*) from tbl_middle_student where a.classid=classid and a.height <height)


//分组以后取前2条
Select * from tbl_middle_student a where 2>(select count(*) from tbl_middle_student where a.classid=classid and a.height <height)