如何获得最小函数结果的列名? [英] How to get the column name of the result of a least function?
问题描述
我有一个雇员表,如下所示:
I have an employee table that looks like this:
| id | name | q1 | q2 | q3 | q4 |
+----+------+----+----+----+----+
| 1 | John | 20 | 30 | 10 | 4 |
| 2 | Ram | 07 | 10 | 03 | 4 |
| 3 | John | 05 | 03 | 15 | 40 |
| 4 | Sree | 12 | 05 | 20 | 25 |
我需要获取id等于4的问题的最小值和最大值.在这种情况下,我需要返回5和25.我使用以下查询实现了这一点:
I needed to get the minimum value and maximum value of questions where the id is equal to 4. In this case, I needed 5 and 25 returned. I acheived that using the following query:
SELECT id, name,
LEAST(q1, q2, q3, q4) AS minValue,
GREATEST(q1, q2, q3, q4) AS maxValue
FROM employee
WHERE id = 4;
但是这不会返回的是问题ID.如何调整查询以显示q2是最小值,q4是最大值?我知道我可以写一个大写的语句,但我也觉得可以通过联接来完成,但我不知道.
But what this doesn't return is the question id. How can I adjust my query to show that q2 is the minimum and q4 is the maximum? I know I could write a big case statement, but I also feel like it could be accomplished using a join but I can't figure it out.
注意:这是针对Postgresql数据库的,但是我也标记了MySQL,因为我知道它也支持LEAST
和GREATEST
函数.如果两者的解决方案都非常不同,那么我将删除此注释并提出一个单独的问题.
Note: This is for a postgresql database, but I tagged MySQL as well because I know it also supports the LEAST
and GREATEST
functions. If the solution is very different for both, then I will remove this note and make a separate question.
编辑
我已经有 SQL小提琴.
推荐答案
您可以使用case
语句:
CASE
WHEN LEAST(q1, q2, q3, q4) = q1 THEN 'q1'
WHEN LEAST(q1, q2, q3, q4) = q2 THEN 'q2'
WHEN LEAST(q1, q2, q3, q4) = q3 THEN 'q3'
ELSE 'q4'
END as minQuestion
(注意:它将失去与领带有关的信息.)
(Note: it will lose information over ties.)
如果您对联系感兴趣,则可以通过子查询和数组来解决问题:
If you're interested in ties, approaching it with a subquery and arrays will do the trick:
with employee as (
select id, q1, q2, q3, q4
from (values
(1, 1, 1, 3, 4),
(2, 4, 3, 1, 1)
) as rows (id, q1, q2, q3, q4)
)
SELECT least(q1, q2, q3, q4),
array(
select q
from (values (q1, 'q1'),
(q2, 'q2'),
(q3, 'q3'),
(q4, 'q4')
) as rows (v, q)
where v = least(q1, q2, q3, q4)
) as minQuestions
FROM employee e
WHERE e.id = 1;
这篇关于如何获得最小函数结果的列名?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!