mysql查询学生名次，如果只查前两名，单纯使用limit 2，遇到有并列第二名，则只能查到一个名次

这是查询到的学生总成绩

如果简单limit 2,即

```

SELECT ts.name ,sum(tg.score) FROM tem_student ts,tem_grade tg WHERE ts.id = tg.id GROUP BY ts.id ORDER BY sum(tg.score) DESC LIMIT 2

```

就不能查询到并列第2的情况

拆分思路

查询所有总分 T1

```

SELECT s.id, s.`name`, SUM(g.score) sumScore FROM tem_student s, tem_grade g WHERE s.id = g.id GROUP BY s.id;

```

查询前2名的总分 T2

```

SELECT SUM(g.score) AS sumScore FROM tem_student s, tem_grade g WHERE s.id = g.id GROUP BY s.id ORDER BY sumScore DESC LIMIT 2;

```

从所有总分中提取（前2名总分）

-- SELECT * FROM T1 WHERE T1.sumScore IN (

-- T2

-- )

```

SELECT t1.* FROM (

  SELECT s.id, s.`name`, SUM(g.score) sumScore FROM tem_student s, tem_grade g WHERE s.id = g.id GROUP BY s.id

) t1 WHERE t1.sumScore IN (

  -- t2

  SELECT * FROM ( SELECT SUM(g.score) AS sumScore FROM tem_student s, tem_grade g WHERE s.id = g.id GROUP BY s.id ORDER BY sumScore DESC LIMIT 2 )  t2

)

```

最终结果：