英文:
how to find the least two column values in a table
问题
我有下面的表格记录：
name | grade1 | grade2 | grade3 | grade4 | least_two |
johny | 30 | 20 | 10 | 15 | |
fredd | 25 | 40 | 30 | 35 | |
我想要一个 SQL 查询，可以找到并求出“四个成绩列中的最小两个值”，然后将结果更新到最后一列。
像下面的示例一样：
name | grade1 | grade2 | grade3 | grade4 | least_two |
johny | 30 | 20 | 10 | 15 | 25 |
fredd | 25 | 40 | 30 | 35 | 55 |
表格名称为 'student_grades'。我正在使用 WampServer。如何编写一个 SQL 语句来实现这个目标？
这是适用于 SQLite 的 SQL 查询：
UPDATE student_grades
SET least_two = (
SELECT MIN(grade)
FROM (
SELECT grade1 AS grade FROM student_grades
UNION ALL
SELECT grade2 FROM student_grades
UNION ALL
SELECT grade3 FROM student_grades
UNION ALL
SELECT grade4 FROM student_grades
) AS grades
WHERE name = student_grades.name
LIMIT 1, 1
);
但是这在 MySQL 服务器上不起作用。
如果要在 MySQL 服务器上执行相同的操作，你可以使用以下 SQL 查询：
UPDATE student_grades AS sg
JOIN (
SELECT id, name, (
SELECT MIN(grade)
FROM (
SELECT grade1 AS grade FROM student_grades
UNION ALL
SELECT grade2 FROM student_grades
UNION ALL
SELECT grade3 FROM student_grades
UNION ALL
SELECT grade4 FROM student_grades
) AS grades
WHERE name = sg.name
LIMIT 1, 1
) AS least_two
FROM student_grades
) AS subquery
ON sg.id = subquery.id
SET sg.least_two = subquery.least_two;
这应该在 MySQL 服务器上正常工作。希望这能帮助你实现你的目标。
英文:
I have table records in an example below:<br><br>
name | grade1 | grade2 | grade3 | grade4 | least_two |<br>
johny | 30 | 20 | 10 | 15 | |<br>
fredd | 25 | 40 | 30 | 35 | |<br><br>
I want an sql query which can find and sum the least two values of the 'four grade columns' and the result updated to the last column.<br>
like in this example below:<br>
name | grade1 | grade2 | grade3 | grade4 | least_two |<br>
johny | 30 | 20 | 10 | 15 | 25 |<br>
fredd | 25 | 40 | 30 | 35 | 55 |<br><br><br>
the least two column values in the first row are :10 and 15. so the last column has a value of 25<br>
and the least two column values in the second row are 25 and 30. it's last column has a value of 55.<br><br>
the table name is 'student_grades'.I am using wampserver. how do I write an sql statement to achieve this.
<br>
this is the sql query that worked for sqlite :
try{
String sql = "update sudent_grades set least_two=(SELECT MIN(grade1,grade2,grade3,grade4) + " +
"( SELECT grade1 AS s FROM sudent_grades t2 WHERE sudent_grades.name = sudent_grades.name UNION ALL " +
" SELECT grade2 FROM sudent_grades t2 WHERE sudent_grades.name = sudent_grades.name UNION ALL" +
" SELECT grade3 FROM sudent_grades t2 WHERE sudent_grades.name = sudent_grades.name UNION ALL" +
" SELECT grade4 FROM sudent_grades t2 WHERE sudent_grades.name = sudent_grades.name ORDER BY s LIMIT 1,1" +
") AS result WHERE name = student_grades.name )";
pst=conn.prepareStatement(sql);
pst.execute();
}catch(SQLException | NumberFormatException e){
JOptionPane.showMessageDialog(null, e);
}finally{
try{
rs.close();
pst.close();
}
catch(Exception e){
}
}
but it doesnt work for mysql server.
答案1
得分: 1
你可以对数据进行解枢轴操作，然后重新汇总：
select name, grade1, grade2, grade3, grade4,
sum(case when seqnum <= 2 then grade end) as least_two;
from (select t.*, row_number() over (partition by name order by grade) as seqnum
from ((select t.*, grade1 as grade
from t
) union all
(select t.*, grade2 as grade
from t
) union all
(select t.*, grade3 as grade
from t
) union all
(select t.*, grade4 as grade
from t
)
) t
) t
group by name, grade1, grade2, grade3, grade4;
数据应存储为每个姓名和成绩一行，可能包括成绩的datetime等其他信息。
英文:
You can unpivot the data and then reaggregate:
select name, grade1, grade2, grade3, grade4,
sum(case when seqnum <= 2 then grade end) as least_two;
from (select t.*, row_number() over (partition by name order by grade) as seqnum
from ((select t.*, grade1 as grade
from t
) union all
(select t.*, grade2 as grade
from t
) union all
(select t.*, grade3 as grade
from t
) union all
(select t.*, grade4 as grade
from t
)
) t
) t
group by name, grade1, grade2, grade3, grade4;
The data should be stored with one row per name and grade -- and perhaps additional information such as the datetime for the grade.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论