关于如何同时比较查询中两列值的 SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13309075/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL on how to simultaneously compare two column values in a query
提问by user1202994
I need create a query to find the names of drivers whose exam scores get lower when they take more exams. So I have the following tables:
我需要创建一个查询来查找参加更多考试时考试分数降低的司机的姓名。所以我有以下表格：
branch(branch_id, branch_name, branch_addr, branch_city, branch_phone);
driver(driver_ssn, driver_name, driver_addr, driver_city, driver_birthdate, driver_phone);
license(license_no, driver_ssn, license_type, license_class, license_expiry, issue_date, branch_id);
exam(driver_ssn, branch_id, exam_date, exam_type, exam_score);
**the exam_date is a date
**exam_date 是一个日期
So I am using the tables driver and exam. I would like to somehow check that the exam_date>anotherDate while at the same time checking that exam_score
所以我使用表驱动程序和考试。我想以某种方式检查exam_date>anotherDate，同时检查exam_score
*EDIT
*编辑
This is what I came up with but I feel like some of the syntax is illegal. I keep getting a syntax error.
这是我想出的，但我觉得有些语法是非法的。我不断收到语法错误。
s.executeQuery("SELECT driver_name " +
"FROM driver " +
"WHERE driver.driver_ssn IN " +
"(SELECT e1.driver_ssn" +
"FROM exam e1" +
"WHERE e1.exam_score < " +
"(SELECT e2.exam_score FROM exam e2)" +
"AND e1.exam_date > " +
"(SELECT e2.exam_date FROM exam e2)");
EDIT! I got it working! Thanks for your input everyone!
编辑！我让它工作了！感谢大家的投入！
SELECT driver.driver_name
FROM driver
WHERE driver.driver_ssn IN
(SELECT e1.driver_ssn
FROM exam e1, exam e2, driver d
WHERE e1.exam_score < e2.exam_score
AND e1.exam_date > e2.exam_date
AND e1.driver_ssn=e2.driver_ssn)
采纳答案by Himanshu
SELECT returns a set and you cannot compare a single value against a set. You can try something along these lines. This is similar to yours and doesn't handle three exam case :-
SELECT 返回一个集合，您不能将单个值与集合进行比较。你可以按照这些思路尝试一些东西。这与您的类似，不处理三个考试案例：-
SELECT driver_name
FROM driver
JOIN exam e1 ON driver_ssn
JOIN exam e2 ON driver_ssn
WHERE e1.exam_score < e2.exam_score
AND e1.exam_date > e2.exam_date
The query selects all pairs of exams taken by a driver in which the score is less and the date big
查询选择驾驶员参加的所有考试，其中分数较小且日期较大
回答by PeteGO
You will need to do a self join. See this example and work it to your schema.
您将需要进行自联接。请参阅此示例并将其应用于您的架构。
select d.name,
es.date_taken as 'prev date',
es.score as 'prev score',
es.date_taken as 'new date',
es_newer.score as 'new score'
from driver d
inner join exam_score es
on d.id = es.driver_id
left outer join exam_score es_newer
on d.id = es_newer.driver_id
and es_newer.date_taken > es.date_taken
and es_newer.score < es.score
where es_newer.id is not null
Here is a SQL Fiddlethat I made to demonstrate.
这是我用来演示的SQL Fiddle。
回答by topchef
Simple take on this problem would be getting drivers who take a couple of exams and their second score is lower.
解决这个问题的简单方法是让司机参加几次考试，但他们的第二个分数较低。
To compare columns from the same table SQL uses self-join. Your join condition should include:
要比较同一表中的列，SQL 使用自联接。您的加入条件应包括：
select e1.driver_ssn, e1.exam_type, e1.exam_score as score_before,
e2.exam_score as score_after
exam e1 join exam e2 on (e1.driver_ssn = e2.driver_ssn and
e1.exam_type = e2.exam_type and
e1.exam_date < e2.exam_date and
e1.exam_score > e2.exam_score)
