求一SQL,两表联合查询! 100分!

Question

学生表A: 学生ID, 学生姓名
成绩表B: 成绩ID, 学生ID, 科目, 分数现要求, 每一个学生取一个自己的最高分数, 按此分数取前10名. 
显示结果如下:学生姓名  科目  分数
张三      数学   100
李四      语文   98
王五      英语   97
......

Answer 1

select a.学生姓名,b.科目,b.分数
from 学生表A a 
join 成绩表B b on a.学生ID=b.学生ID
where b.学生ID in(select b.学生ID from 成绩表B where 学生ID=b.学生ID order by 分数 desc limit 1)
order by b.分数 desc
limit 10;

Answer 2

修正笔误
select a.学生姓名,b.科目,b.分数
from 学生表A a 
join 成绩表B b on a.学生ID=b.学生ID
where b.学生ID in(select 学生ID from 成绩表B where 学生ID=b.学生ID order by 分数 desc limit 1)
order by b.分数 desc
limit 10;

Answer 3

TO josy. 建表试验CREATE TABLE IF NOT EXISTS `student` (
  `stuId` int(4) NOT NULL,
  `stuName` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=gbk;INSERT INTO `student` (`stuId`, `stuName`) VALUES
(1001, '张三'),
(1002, '小二'),
(1003, '李四'),
(1004, '王五'),
(1005, '刘六'),
(1006, '陈七'),
(1007, '杨八'),
(1008, '凌晨'),
(1009, '王小小'),
(1010, '潘婷'),
(1011, '吴江'),
(1012, '邓思浩');CREATE TABLE IF NOT EXISTS `grade` (
  `gid` int(8) NOT NULL AUTO_INCREMENT,
  `stuId` int(4) NOT NULL,
  `subject` varchar(20) NOT NULL,
  `score` int(3) NOT NULL,
  PRIMARY KEY (`gid`)
) ENGINE=InnoDB  DEFAULT CHARSET=gbk;INSERT INTO `grade` (`gid`, `stuId`, `subject`, `score`) VALUES
(1, 1001, '语文', 78),
(2, 1001, '数学', 91),
(3, 1001, '英语', 87),
(4, 1001, '物理', 64),
(5, 1001, '化学', 59),
(6, 1002, '语文', 76),
(7, 1002, '数学', 99),
(8, 1002, '英语', 87),
(9, 1002, '物理', 69),
(10, 1002, '化学', 87),
(11, 1003, '语文', 75),
(12, 1003, '数学', 69),
(13, 1003, '英语', 85),
(14, 1003, '物理', 87),
(15, 1003, '化学', 58),
(16, 1004, '语文', 76),
(17, 1004, '数学', 88),
(18, 1004, '英语', 77),
(19, 1004, '物理', 68),
(20, 1004, '化学', 65),
(21, 1005, '语文', 70),
(22, 1005, '数学', 34),
(23, 1005, '英语', 54),
(24, 1005, '物理', 66),
(25, 1005, '化学', 77),
(26, 1006, '语文', 76),
(27, 1006, '数学', 98),
(28, 1006, '英语', 80),
(29, 1006, '物理', 87),
(30, 1006, '化学', 89),
(31, 1007, '语文', 68),
(32, 1007, '数学', 81),
(33, 1007, '英语', 84),
(34, 1007, '物理', 61),
(35, 1007, '化学', 77),
(36, 1008, '语文', 88),
(37, 1008, '数学', 95),
(38, 1008, '英语', 79),
(39, 1008, '物理', 66),
(40, 1008, '化学', 76),
(41, 1009, '语文', 76),
(42, 1009, '数学', 31),
(43, 1009, '英语', 84),
(44, 1009, '物理', 60),
(45, 1009, '化学', 87),
(46, 1010, '语文', 76),
(47, 1010, '数学', 73),
(48, 1010, '英语', 88),
(49, 1010, '物理', 67),
(50, 1010, '化学', 92),
(51, 1011, '语文', 86),
(52, 1011, '数学', 54),
(53, 1011, '英语', 68),
(54, 1011, '物理', 63),
(55, 1011, '化学', 89),
(56, 1012, '语文', 64),
(57, 1012, '数学', 46),
(58, 1012, '英语', 93),
(59, 1012, '物理', 87),
(60, 1012, '化学', 78);

Answer 4

试验SQL: select a.stuName,b.subject,b.score
from student a 
join grade b on a.stuId=b.stuId
where b.stuId in(select stuId from grade where stuId=b.stuId order by score desc limit 1)
order by b.score desc
limit 10结果提示:
This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Answer 5

select stuId,stuName,
(select max(score) from grade where stuId=student.stuId) as highScore
from student
order by highScore desc
limit 10;
mysql> select stuId,stuName,
    ->  (select max(score) from grade where stuId=student.stuId) as highScore
    -> from student
    -> order by highScore desc
    -> limit 10;
+-------+---------+-----------+
| stuId | stuName | highScore |
+-------+---------+-----------+
|  1002 | 小二    |        99 |
|  1006 | 陈七    |        98 |
|  1008 | 凌晨    |        95 |
|  1012 | 邓思浩  |        93 |
|  1010 | 潘婷    |        92 |
|  1001 | 张三    |        91 |
|  1011 | 吴江    |        89 |
|  1004 | 王五    |        88 |
|  1009 | 王小小  |        87 |
|  1003 | 李四    |        87 |
+-------+---------+-----------+
10 rows in set (0.08 sec)mysql>

Answer 6

或者
mysql> select stuId,stuName,max(score) as highScore
    -> from student a inner join grade b using(stuId)
    -> group by stuId,stuName
    -> order by highScore desc
    -> limit 10;
+-------+---------+-----------+
| stuId | stuName | highScore |
+-------+---------+-----------+
|  1002 | 小二    |        99 |
|  1006 | 陈七    |        98 |
|  1008 | 凌晨    |        95 |
|  1012 | 邓思浩  |        93 |
|  1010 | 潘婷    |        92 |
|  1001 | 张三    |        91 |
|  1011 | 吴江    |        89 |
|  1004 | 王五    |        88 |
|  1003 | 李四    |        87 |
|  1009 | 王小小  |        87 |
+-------+---------+-----------+
10 rows in set (0.09 sec)mysql>

Answer 7

TO ACMAIN_CHM: 成绩表中,我要列两个字段出来, 一是分数, 二是科目(在实际应用中,也有可能是成绩ID). 这个SQL怎么写?

Answer 8

select s.stuName,t.subject,t.score
from student s,
(select * from grade g
where not exists
(select 1 from grade where stuId=g.stuId
and score>g.score)) t
where s.stuId=t.stuId
order by t.score desc
limit 10;

Answer 9

TO ACMAIN_CHM: 成绩表中,我要列两个字段出来, 一是分数, 二是科目(在实际应用中,也有可能是成绩ID). 这个SQL怎么写?
这次你想要什么就自己选吧mysql> select *
    -> from student a inner join grade b using(stuId)
    -> where not exists (select 1
    -> from grade
    -> where stuId=b.stuId
    -> and score>b.score);
+-------+---------+-----+---------+-------+
| stuId | stuName | gid | subject | score |
+-------+---------+-----+---------+-------+
|  1001 | 张三    |   2 | 数学    |    91 |
|  1002 | 小二    |   7 | 数学    |    99 |
|  1003 | 李四    |  14 | 物理    |    87 |
|  1004 | 王五    |  17 | 数学    |    88 |
|  1005 | 刘六    |  25 | 化学    |    77 |
|  1006 | 陈七    |  27 | 数学    |    98 |
|  1007 | 杨八    |  33 | 英语    |    84 |
|  1008 | 凌晨    |  37 | 数学    |    95 |
|  1009 | 王小小  |  45 | 化学    |    87 |
|  1010 | 潘婷    |  50 | 化学    |    92 |
|  1011 | 吴江    |  55 | 化学    |    89 |
|  1012 | 邓思浩  |  58 | 英语    |    93 |
+-------+---------+-----+---------+-------+
12 rows in set (0.00 sec)mysql>

Answer 10

12楼的经试验, 可以得出我想要的结果, 只是想请解惑一下, 下面的SQL语句怎解?
(select * from grade g
where not exists
(select 1 from grade where stuId=g.stuId
and score>g.score)) t

Answer 11

或者mysql> select a.stuName,b.subject,b.score
    -> from student a inner join grade b using(stuId)
    ->  inner join (select stuId,max(score) as score from grade group by stuId) c
    ->          using(stuId,score);
+---------+---------+-------+
| stuName | subject | score |
+---------+---------+-------+
| 张三    | 数学    |    91 |
| 小二    | 数学    |    99 |
| 李四    | 物理    |    87 |
| 王五    | 数学    |    88 |
| 刘六    | 化学    |    77 |
| 陈七    | 数学    |    98 |
| 杨八    | 英语    |    84 |
| 凌晨    | 数学    |    95 |
| 王小小  | 化学    |    87 |
| 潘婷    | 化学    |    92 |
| 吴江    | 化学    |    89 |
| 邓思浩  | 英语    |    93 |
+---------+---------+-------+
12 rows in set (0.08 sec)mysql>