select TOP 1 id,class_room,student_name,student_score from [A] ORDER BY student_score DESC
select * from a where student_score in (select max(student_score) from a group by class_room)
sorry!没看清题目 :P同意 bigpig(终极@老公) 的写法!
SQL> select * from 2 t1 a 3 where 4 (class_room,student_score) in 5 (select 6 b.class_room as class_room, 7 max(b.student_score) as student_score 8 from 9 t1 b 10 group by 11 class_room) 12 ;ID CLASS_ROOM STUDENT_NA STUDENT_SCORE ----- ---------- ---------- ------------- 00001 11 a 685 00004 12 b 22223
应该是:SELECT [ID],class_room ,student_name ,student_score FROM YourTable1 a WHERE student_score IN (SELECT MAX(b.student_score) AS MAXScore FROM YourTable1 b GROUP BY b.class_room HAVING a.class_room =b.class_room )
对于LXCC大侠的写法,我有不明白的地方,请指教:从理论上说,这条子查询的执行过程,应该是由内而外是吧?那么也就是说: IN 后面的(里的内容先运行。那么,运行得出的结果比方说是(88,76,99)这样的话,放到整个语句中,那不是还是与上面你说的那个错误的别人的语句是一样的么?外面的查询语句,也不知道里面的分数是哪个班儿的最高分??SELECT [ID],class_room ,student_name ,student_score FROM YourTable1 a WHERE student_score IN (SELECT MAX(b.student_score) AS MAXScore FROM YourTable1 b GROUP BY b.class_room HAVING a.class_room =b.class_room )
关键在 HAVING a.[Name]=b.[Name] 即使最大值集合中的Name值,与搜索的Name值相符合
汗!贴错了!关键在 HAVING a.class_room =b.class_room 即使最大值集合中的class_room值,与搜索的class_room值相符合
ID class_room student_name student_score 2 三班 张欣 88 3 六班 李乐 76 4 八班 于民工 99 6 一班 杨彩霞 28 7 三班 李达成 86 8 五班 王友爱 87 9 三班 杨琥 88 ========================================== Select * from tlb A, (slelect class_room,Max(student_score) from tbl group by class_room ) As B where A.classroom=B.classroom and A.student_score=B.student_score
测试过的 Select A.* from tlb A, (slelect class_room,Max(student_score) As student_score from tbl group by class_room ) As B where A.classroom=B.classroom and A.student_score=B.student_score
ID class_room student_name student_score 2 三班 张欣 88 3 六班 李乐 76 4 八班 于民工 99 6 一班 杨彩霞 28 7 三班 李达成 86 8 五班 王友爱 87 9 三班 杨琥 88SELECT distinct id , CLASS_ROOM, student_name, student_score FROM TABLE1 WHERE STUDENT_SCORE IN(SELECT MAX(STUDENT_SCORE) FROM TABLE1 group by class_room)按班级把每个班的最高成绩找出来,如果有多个地一名,都找出来
select max(strdent_score) from a group by class_room
2 三班 张欣 88
3 六班 李乐 76
4 八班 于民工 99
6 一班 杨彩霞 28
7 三班 李达成 86
8 五班 王友爱 87
9 三班 杨琥 88
2 t1 a
3 where
4 (class_room,student_score) in
5 (select
6 b.class_room as class_room,
7 max(b.student_score) as student_score
8 from
9 t1 b
10 group by
11 class_room)
12 ;ID CLASS_ROOM STUDENT_NA STUDENT_SCORE
----- ---------- ---------- -------------
00001 11 a 685
00004 12 b 22223
FROM YourTable1 a
WHERE student_score IN
(SELECT MAX(b.student_score) AS MAXScore
FROM YourTable1 b
GROUP BY b.class_room HAVING a.class_room =b.class_room )
这个好
对LXCC的写法,我稍后试一下,应该可以
-------------------稍候,运行通过后,马上给分。谢谢各位。
FROM YourTable1 a
WHERE student_score IN
(SELECT MAX(b.student_score) AS MAXScore
FROM YourTable1 b
GROUP BY b.class_room HAVING a.class_room =b.class_room )
即使最大值集合中的Name值,与搜索的Name值相符合
即使最大值集合中的class_room值,与搜索的class_room值相符合
如果是同步运行,这条语句的执行结果就是对的了。不过不确定是不这么回事呀。倒底是怎么详细运行的啊?还是DBMS能说个清楚。
给分吧.
2 三班 张欣 88
3 六班 李乐 76
4 八班 于民工 99
6 一班 杨彩霞 28
7 三班 李达成 86
8 五班 王友爱 87
9 三班 杨琥 88
==========================================
Select * from tlb A,
(slelect class_room,Max(student_score) from tbl
group by class_room
) As B
where A.classroom=B.classroom and A.student_score=B.student_score
Select A.* from tlb A,
(slelect class_room,Max(student_score) As student_score from tbl
group by class_room
) As B
where A.classroom=B.classroom and A.student_score=B.student_score
2 三班 张欣 88
3 六班 李乐 76
4 八班 于民工 99
6 一班 杨彩霞 28
7 三班 李达成 86
8 五班 王友爱 87
9 三班 杨琥 88SELECT distinct id , CLASS_ROOM, student_name, student_score FROM TABLE1 WHERE STUDENT_SCORE IN(SELECT MAX(STUDENT_SCORE) FROM TABLE1 group by class_room)按班级把每个班的最高成绩找出来,如果有多个地一名,都找出来