SQL sql查询以获取最早日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2393573/
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 query to get earliest date
提问by leora
If I have a table with columns id, name, score, date
如果我有一个包含列的表id，name, score,date
and I wanted to run a sql query to get the record where id = 2with the earliest date in the data set.
我想运行一个 sql 查询来获取id = 2数据集中最早日期的记录。
Can you do this within the query or do you need to loop after the fact?
您可以在查询中执行此操作还是需要在事后循环？
I want to get all of the fields of that record..
我想获取该记录的所有字段..
回答by Zyphrax
If you just want the date:
如果您只想要日期：
SELECT MIN(date) as EarliestDate
FROM YourTable
WHERE id = 2
If you want all of the information:
如果您想要所有信息：
SELECT TOP 1 id, name, score, date
FROM YourTable
WHERE id = 2
ORDER BY Date
Prevent loops when you can. Loops often lead to cursors, and cursors are almost never necessary and very often really inefficient.
尽可能防止循环。循环通常会导致游标，而游标几乎从不需要，而且通常非常低效。
回答by shahkalpesh
SELECT TOP 1 ID, Name, Score, [Date]
FROM myTable
WHERE ID = 2
Order BY [Date]
回答by Aaron
Try
尝试
select * from dataset
where id = 2
order by date limit 1
Been a while since I did sql, so this might need some tweaking.
自从我做 sql 以来已经有一段时间了，所以这可能需要一些调整。
回答by Jakub
Using "limit" and "top" will not work with all SQL servers (for example with Oracle). You can try a more complex query in pure sql:
使用“limit”和“top”不适用于所有 SQL 服务器（例如 Oracle）。你可以在纯sql中尝试更复杂的查询：
select mt1.id, mt1."name", mt1.score, mt1."date" from mytable mt1
where mt1.id=2
and mt1."date"= (select min(mt2."date") from mytable mt2 where mt2.id=2)
回答by Kevin Swann
While using TOP or a sub-query both work, I would break the problem into steps:
虽然使用 TOP 或子查询都可以工作，但我会将问题分解为以下步骤：
Find target record
查找目标记录
SELECT MIN( date ) AS date, id
FROM myTable
WHERE id = 2
GROUP BY id
Join to get other fields
加入以获取其他领域
SELECT mt.id, mt.name, mt.score, mt.date
FROM myTable mt
INNER JOIN
(
SELECT MIN( date ) AS date, id
FROM myTable
WHERE id = 2
GROUP BY id
) x ON x.date = mt.date AND x.id = mt.id
While this solution, using derived tables, is longer, it is:
虽然这个使用派生表的解决方案更长，但它是：
- Easier to test
- Self documenting
- Extendable
- 更容易测试
- 自我记录
- 可扩展
It is easier to test as parts of the query can be run standalone.
由于查询的部分可以独立运行，因此测试更容易。
It is self documenting as the query directly reflects the requirement ie the derived table lists the row where id = 2 with the earliest date.
它是自我记录的，因为查询直接反映了需求，即派生表列出了 id = 2 的行和最早的日期。
It is extendable as if another condition is required, this can be easily added to the derived table.
它是可扩展的，就好像需要另一个条件一样，这可以很容易地添加到派生表中。
