mysql -从多个列中获取两个最大值

Question

我们可以使用GREATEST从多个列中获得最大值，如下所示

SELECT GREATEST(mark1,mark2,mark3,mark4,mark5) AS best_mark FROM marks

但是现在我想从所有的(5)分中得到两个最好的分数。

我可以在mysql查询上这样做吗？

表结构(我知道它是错误的-由某人创建)：

student_id  | Name | mark1 | mark2 | mark3 | mark4 | mark5

Answer 1

这不是最优雅的解决方案，但是如果您不能改变表结构，那么可以取消数据透视，然后应用用户定义的变量来获得每个student_id的行号。代码将类似于以下代码：

select student_id, name, col, data
from
(
  SELECT student_id, name, col,
    data,
    @rn:=case when student_id = @prev then @rn else 0 end +1 rn,
    @prev:=student_id
  FROM 
  (
    SELECT student_id, name, col,
      @rn, 
      @prev,
      CASE s.col
        WHEN 'mark1' THEN mark1
        WHEN 'mark2' THEN mark2
        WHEN 'mark3' THEN mark3
        WHEN 'mark4' THEN mark4
        WHEN 'mark5' THEN mark5
      END AS DATA
    FROM marks
    CROSS JOIN 
    (
      SELECT 'mark1' AS col UNION ALL 
      SELECT 'mark2' UNION ALL 
      SELECT 'mark3' UNION ALL 
      SELECT 'mark4' UNION ALL 
      SELECT 'mark5'
    ) s
    cross join (select @rn := 0, @prev:=0) c
  ) s
  order by student_id, data desc
) d
where rn <= 2
order by student_id, data desc;

参见SQL Fiddle with Demo。这将返回每个student_id的前2个标记。内部子查询执行与使用UNION ALL取消透视类似的功能，但是您不需要对表进行多次查询来获得结果。

Answer 2

我认为你应该改变你的数据库结构，因为有那么多的水平标记(例如，作为字段/列)已经意味着你做错了什么。

相反，将所有标记放在一个单独的表中，在该表中创建多对多关系，然后与LIMIT一起执行必要的SELECT。

建议：

1. 创建一个称为mark_types的表。列：id、mark_type。我看到您当前有5种类型的标记；添加其他类型将非常简单。
2. 更改您的marks表以保存3列：id、mark/grade/value，mark_type (此列借助连接和GROUP BY mark_type.

对SELECT查询进行外部约束

Answer 3

您可以创建一个临时表，然后

Create a temporary table in a SELECT statement without a separate CREATE TABLE

按如下方式查询该表

从temp ORDER BY标记DESC中选择TOP 2*

然后

删除临时表