Oracle - 选择子集中具有最小值的行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15791195/
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
Oracle - select rows with minimal value in a subset
提问by kyooryu
I have a following table of dates:
我有以下日期表：
dateID INT (PK),
personID INT (FK),
date DATE,
starttime VARCHAR, --Always in a format of 'HH:MM'
What I want to do is I want to pull rows (all columns, including PK) with lowest date (primary condition) and starttime (secondary condition) for every person. For example, if we have
我想要做的是我想为每个人提取具有最低日期（主要条件）和开始时间（次要条件）的行（所有列，包括 PK）。例如，如果我们有
row1(date = '2013-04-01' and starttime = '14:00')
row1(date = '2013-04-01' and starttime = '14:00')
and
和
row2(date = '2013-04-02' and starttime = '08:00')
row2(date = '2013-04-02' and starttime = '08:00')
row1 will be retrieved, along with all other columns.
row1 将与所有其他列一起被检索。
So far I have come up with gradual filtering the table, but it`s quite a mess. Is there more efficient way of doing this?
到目前为止，我已经提出了逐步过滤表格，但它很混乱。有没有更有效的方法来做到这一点？
Here is what I made so far:
这是我到目前为止所做的：
SELECT
D.id
, D.personid
, D.date
, D.starttime
FROM table D
JOIN (
SELECT --Select lowest time from the subset of lowest dates
A.personid,
B.startdate,
MIN(A.starttime) AS starttime
FROM table A
JOIN (
SELECT --Select lowest date for every person to exclude them from outer table
personid
, MIN(date) AS startdate
FROM table
GROUP BY personid
) B
ON A.personid = B.peronid
AND A.date = B.startdate
GROUP BY
A.personid,
B.startdate
) C
ON C.personid = D.personid
AND C.startdate = D.date
AND C.starttime = D.starttime
It works, but I think there is a more clean/efficient way to do this. Any ideas?
它有效，但我认为有一种更干净/更有效的方法来做到这一点。有任何想法吗？
EDIT: Let me expand a question - I also need to extract maximum date (only date, without time) for each person.
编辑：让我扩展一个问题 - 我还需要为每个人提取最大日期（只有日期，没有时间）。
The result should look like this:
结果应如下所示：
id
personid
max(date) for each person
min(date) for each person
min(starttime) for min(date) for each person
It is a part of a much larger query (the resulting table is joined with it), and the resulting table must be lightweight enough so that the query won`t execute for too long. With single join with this table (just using min, max for each field I wanted) the query took about 3 seconds, and I would like the resulting query not to take longer than 2-3 times that.
它是一个更大的查询的一部分（结果表与其连接），结果表必须足够轻量级，以便查询不会执行太长时间。通过与该表的单一连接（仅对我想要的每个字段使用 min、max），查询耗时大约 3 秒，我希望生成的查询不超过 2-3 倍。
回答by DazzaL
you should be able to do this like:
你应该能够这样做：
select a.dateID, a.personID, a.date, a.max_date, a.starttime
from (select t.*,
max(t.date) over (partition by t.personID) max_date,
row_number() over (partition by t.personID
order by t.date, t.starttime) rn
from table t) a
where a.rn = 1;
sample data added to fiddle: http://sqlfiddle.com/#!4/63c45/1
回答by Santhosh
This is the query you can use and no need to incorporate in your query. You can also use @Dazzal's query as stand alone
这是您可以使用的查询，无需合并到您的查询中。您也可以单独使用@Dazzal 的查询
SELECT ID, PERSONID, DATE, STARTTIME
(
SELECT ID, PERONID, DATE, STARTTIME, ROW_NUMBER() OVER(PARTITION BY personid ORDER BY STARTTIME, DATE) AS RN
FROM TABLE
) A
WHERE
RN = 1
回答by Chandra
select a.id,a.accomp, a.accomp_name, a.start_year,a.end_year, a.company
from (select t.*,
min(t.start_year) over (partition by t.company) min_date,
max(t.end_year) over (partition by t.company) max_date,
row_number() over (partition by t.company
order by t.end_year desc) rn
from temp_123 t) a
where a.rn = 1;
