我们可以使用GREATEST从多个列中获取最大值,例如
SELECT GREATEST(mark1,mark2,mark3,mark4,mark5) AS best_mark FROM marks
但现在我想在所有(5)分中获得两个最高分。
我可以在mysql查询上这样做吗?
表结构(我知道这是错误的-由某人创建):
student_id | Name | mark1 | mark2 | mark3 | mark4 | mark5
这不是最优雅的解决方案,但如果您不能更改表结构,那么您可以unpivot数据,然后应用用户定义的变量来获取每个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解除枢轴操作类似,但您不需要多次查询该表以获得结果。
我认为你应该改变你的数据库结构,因为有那么多标记水平(即字段/列)已经意味着你做错了。
相反,把所有的标记放在一个单独的表中,在那里你创建了一个多对多的关系,然后执行必要的SELECT
和LIMIT
。
建议:
- 创建一个名为
mark_types
的表。列:id
,mark_type
。我看到你目前有5种类型的标记;这很简单 - 修改
marks
表为3列:id
,mark
/grade
/value
,mark_type
(此列外约束为mark_types
)。 - 在连接的帮助下编写
SELECT
查询和GROUP BY mark_type
。
可以创建一个临时表,然后
在SELECT语句中创建临时表,不需要单独的Create table
按如下方式查询
SELECT TOP 2 * FROM tempORDER BY mark DESC
然后删除临时表
好的,这是一个新的答案,应该与当前的表结构:
SELECT `student_id`, `Name`, `mark` FROM (SELECT `student_id`, `Name`, `mark1` AS `mark` FROM `marks`
UNION ALL
SELECT `student_id`, `Name`, `mark2` AS `mark` FROM `marks`
UNION ALL
SELECT `student_id`, `Name`, `mark3` AS `mark` FROM `marks`
UNION ALL
SELECT `student_id`, `Name`, `mark4` AS `mark` FROM `marks`
UNION ALL
SELECT `student_id`, `Name`, `mark5` AS `mark` FROM `marks`) AS `marks`
ORDER BY `mark` DESC
LIMIT 2