在mysql的旧版本中，可以使用变量来实现此目的：

select name, sum(q)
from (select nq.*,
             (@rn := if(@n = name, @rn + 1,
                        if(@n := name, 1, 1)
                       )
             ) as rn
      from (select nq.*
            from ((select name, q1 as q from t
                  ) union all
                  (select name, q2 as q from t
                  ) union all
                  (select name, q3 as q from t
                  ) union all
                  (select name, q4 as q from t
                  )
                 ) nq
            order by name, q desc
           ) nq cross join
           (select @n := '', @rn := 0) params
      ) nq
where rn <= 2;

标准化设计可能如下所示：

name q score
Alex  1  5
Alex 2  4
Alex 3 10
Alex 4  7

如评论中的@shadow所述。。你的数据库需要重新构造。。因为那不是数据库工作。。你可以重新构造并做出这样的设计。。

+-------+----+--------+
| name  |  q | point  |
+-------+----+--------+
| Alex  |  1 |      5 |
| Alex  |  2 |      4 |
| Alex  |  3 |     10 |
| Alex  |  4 |      7 |
| Brown |  1 |      7 |
| Brown |  2 |      6 |
| Brown |  3 |      4 |
| Brown |  4 |      1 |
| Chris |  1 |     10 |
| Chris |  2 |     10 |
| Chris |  3 |      9 |
| Chris |  4 |     10 |
| Dave  |  1 |      8 |
| Dave  |  2 |      4 |
| Dave  |  3 |      6 |
| Dave  |  4 |      0 |
+-------+----+--------+

对于查询，您可以这样做：

select
     name, sum(point)
from(
     select 
          name, q, point,
          ROW_NUMBER() OVER (PARTITION BY name ORDER BY point DESC) as ranked
     from newstudents) rankedSD
where
     ranked in (1,2)
group by 
     name

您可以在此处查看演示：
演示<>小提琴
编辑：您可以使用 Window Function . 可以读取row\ u number（）函数