MySQL 选择sql中的行，每个ID的最新日期重复多次
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45381743/
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
select rows in sql with latest date for each ID repeated multiple times
提问by Earthshaker
I have a table where each ID is repeated 3 times. there is a date in front of each id in each row.
I want to select entire row for each ID where date is latest.
There are total 370 columns in this table i want all columns to get selected when i select that row.
我有一个表，其中每个 ID 重复 3 次。每行中的每个 id 前面都有一个日期。
我想为每个日期最新的 ID 选择整行。此表中共有 370 列，我希望在选择该行时选择所有列。
Sample -
样本 -
ID Name Date Marks .. .. ..
1 XY 4/3/2017 27
1 fv 4/3/2014 98
1 jk 4/3/2016 09
2 RF 4/12/2015 87
2 kk 4/3/2009 56
2 PP 4/3/2011 76
3 ee 4/3/2001 12
3 ppp 4/3/2003 09
3 lll 4/3/2011 23
The Answer should be
答案应该是
ID Name Date Marks .. .. ..
1 XY 4/3/2017 27
2 RF 4/12/2015 87
3 lll 4/3/2011 23
I am attempting as below -
我正在尝试如下 -
select distinct ID,*,max(date) as maxdate from table
Also i am trying this in Hive . so not sure if some sql functions dont work in Hive
我也在 Hive 中尝试这个。所以不确定某些 sql 函数是否在 Hive 中不起作用
Thanks
谢谢
回答by ecarlin
This question has been asked before. Please see thisquestion.
这个问题以前有人问过。请看这个问题。
Using the accepted answer and adapting it to your problem you get:
使用已接受的答案并使其适应您的问题，您会得到：
SELECT tt.*
FROM myTable tt
INNER JOIN
(SELECT ID, MAX(Date) AS MaxDateTime
FROM myTable
GROUP BY ID) groupedtt
ON tt.ID = groupedtt.ID
AND tt.Date = groupedtt.MaxDateTime
回答by Oto Shavadze
One way is:
一种方法是：
select table.*
from table
join
(
select ID, max(Date) as max_dt
from table
group by ID
) t
on table.ID= t.ID and table.Date = t.max_dt
Note that if you have multiple equally higher dates for same ID, then you will get all those rows in result
请注意，如果您有多个相同 ID 的同样更高的日期，那么您将在结果中获得所有这些行
回答by RichGoldMD
You can use a join to do this
您可以使用连接来执行此操作
SELECT t1.* from myTable t1
LEFT OUTER JOIN myTable t2 on t2.ID=t1.ID AND t2.`Date` > t1.`Date`
WHERE t2.`Date` IS NULL;
Only rows which have the latest date for each ID with have a NULL join to t2.
只有每个 ID 具有最新日期的行才具有到 t2 的 NULL 连接。
回答by Andrew
Here's one way. The inner query gets the max date for each id. Then you can join that back to your main table to get the rows that match.
这是一种方法。内部查询获取每个 id 的最大日期。然后，您可以将其连接回主表以获取匹配的行。
select
*
from
<your table>
inner join
(select id, max(<date col> as max_date) m
where yourtable.id = m.id
and yourtable.datecolumn = m.max_date)
回答by JNevill
You can do this with a Correlated Subquery (That is a subquery wherein you reference a field in the main query). In this case:
您可以使用相关子查询（即在其中引用主查询中的字段的子查询）来执行此操作。在这种情况下：
SELECT *
FROM yourtable t1
WHERE date = (SELECT max(date) from yourtable WHERE id = t1.id)
Here we give the yourtabletable an alias of t1and then use that alias in the subquery grabbing the max(date)from the same table yourtablefor that id.
这里我们给yourtable表一个别名，t1然后在子查询中使用该别名max(date)从同一个表中yourtable获取id。
回答by milton
Have you tried the following:
您是否尝试过以下操作：
SELECT ID, COUNT(*), max(date)
FROM table
GROUP BY ID;
