/**//*-----------实现对科目分组下显示最大的成绩算法---------------*/ createtable liangfei2(学号 varchar(10),姓名 varchar(10),科目 varchar(10), 成绩 int)----建表 insert liangfei2 select'1','张三','语文','80'unionall select'2','李四','语文','83'unionall select'3','王五','英语','99'unionall select'4','李四','数学','88'unionall select'5','张三','英语','66'unionall select'6','王五','数学','87'unionall select'7','李四','英语','69'unionall select'8','张三','数学','63'unionall select'9','王五','语文','77' go select liangfei2.* from (selectmax(成绩) as mt,科目----------------加入分组列,分组列对应的信息列(我本写的学号,对于GROUP BY ,在SELECT中不能用非分组列) from liangfei2 groupby 科目) as b,liangfei2 where liangfei2.科目=b.科目 and b.mt=liangfei2.成绩 go select* from liangfei2 where (科目,成绩) in (select 科目,max(成绩)as mt from liangfei2 groupby 科目) go select* from liangfei2 wherein----------------- (selectmax(成绩) from liangfei2 groupby 科目) go select*---------------SMART的算法,但性能不行 from liangfei2 a wherenotexists (select1from liangfei2 where 科目=a.科目 and 成绩>a.成绩)