为另一列的每个不同值获取某列的Max值的行

表:

UserId, Value, Date.

我想获得UserId，为每个UserId的最大值(日期)的值。也就是说，具有最新日期的每个UserId的值。有没有一种方法可以在SQL中简单地做到这一点?(最好是Oracle)

为任何歧义道歉:我需要得到所有的用户id。但是对于每个UserId，只有该用户拥有最新日期的行。

662569 次浏览
小开

我想是这样的。(请原谅我的语法错误;在这一点上，我习惯使用HQL !)

编辑:也误解了问题!修正了查询…

SELECT UserId, Value
FROM Users AS user
WHERE Date = (
SELECT MAX(Date)
FROM Users AS maxtest
WHERE maxtest.UserId = user.UserId
)
小开

我不知道你的列的确切名称，但它应该是这样的:

SELECT userid, value
FROM users u1
WHERE date = (
SELECT MAX(date)
FROM users u2
WHERE u1.userid = u2.userid
)
小开

(T-SQL)首先获取所有用户及其最大日期。与表连接以查找maxdates上用户的对应值。

create table users (userid int , value int , date datetime)
insert into users values (1, 1, '20010101')
insert into users values (1, 2, '20020101')
insert into users values (2, 1, '20010101')
insert into users values (2, 3, '20030101')


select T1.userid, T1.value, T1.date
from users T1,
(select max(date) as maxdate, userid from users group by userid) T2
where T1.userid= T2.userid and T1.date = T2.maxdate

结果:

userid      value       date
----------- ----------- --------------------------
2           3           2003-01-01 00:00:00.000
1           2           2002-01-01 00:00:00.000
小开
最佳答案

这将检索my_date列值等于该用户id的my_date最大值的所有行。这可能会为最大日期位于多行上的用户id检索多行。

select userid,
my_date,
...
from
(
select userid,
my_date,
...
max(my_date) over (partition by userid) max_my_date
from   users
)
where my_date = max_my_date

“解析函数摇滚”

编辑:关于第一条评论……

“使用分析查询和自连接违背了分析查询的目的”

这段代码中没有自连接。相反，在包含分析函数的内联视图的结果上放置了一个谓词——这是非常不同的事情，完全是标准的实践。

Oracle的默认窗口是从分区的第一行到当前一行

加窗条款仅适用于存在按顺序条款的情况。如果没有按子句排序，默认情况下就不会应用任何窗口子句，也不能显式地指定任何窗口子句。

代码可以工作。

小开

如果(UserID, Date)是唯一的，即同一个用户没有出现两次日期，则:

select TheTable.UserID, TheTable.Value
from TheTable inner join (select UserID, max([Date]) MaxDate
from TheTable
group by UserID) UserMaxDate
on TheTable.UserID = UserMaxDate.UserID
TheTable.[Date] = UserMaxDate.MaxDate;
小开

我认为你应该对之前的查询进行修改:

SELECT UserId, Value FROM Users U1 WHERE
Date = ( SELECT MAX(Date)    FROM Users where UserId = U1.UserId)
小开

假设Date对于给定的UserID是唯一的，下面是一些TSQL:

SELECT
UserTest.UserID, UserTest.Value
FROM UserTest
INNER JOIN
(
SELECT UserID, MAX(Date) MaxDate
FROM UserTest
GROUP BY UserID
) Dates
ON UserTest.UserID = Dates.UserID
AND UserTest.Date = Dates.MaxDate
小开 
Select
UserID,
Value,
Date
From
Table,
(
Select
UserID,
Max(Date) as MDate
From
Table
Group by
UserID
) as subQuery
Where
Table.UserID = subQuery.UserID and
Table.Date = subQuery.mDate
小开 
select userid, value, date
from thetable t1 ,
( select t2.userid, max(t2.date) date2
from thetable t2
group by t2.userid ) t3
where t3.userid t1.userid and
t3.date2 = t1.date

恕我直言，这是可行的。HTH

小开

我想这应该有用吧?

Select
T1.UserId,
(Select Top 1 T2.Value From Table T2 Where T2.UserId = T1.UserId Order By Date Desc) As 'Value'
From
Table T1
Group By
T1.UserId
Order By
T1.UserId
小开

这应该非常简单:

SELECT UserId, Value
FROM Users u
WHERE Date = (SELECT MAX(Date) FROM Users WHERE UserID = u.UserID)
小开

首先，我看错了问题，下面是一个完整的例子，结果是正确的:

CREATE TABLE table_name (id int, the_value varchar(2), the_date datetime);


INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'a','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(1 ,'b','2/2/2002');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'c','1/1/2000');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'d','3/3/2003');
INSERT INTO table_name (id,the_value,the_date) VALUES(2 ,'e','3/3/2003');

--

  select id, the_value
from table_name u1
where the_date = (select max(the_date)
from table_name u2
where u1.id = u2.id)

--

id          the_value
----------- ---------
2           d
2           e
1           b


(3 row(s) affected)
小开 
SELECT userid, MAX(value) KEEP (DENSE_RANK FIRST ORDER BY date DESC)
FROM table
GROUP BY userid
小开

我知道你要求使用Oracle，但是在SQL 2005中我们现在使用这个:


-- Single Value
;WITH ByDate
AS (
SELECT UserId, Value, ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) RowNum
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE RowNum = 1


-- Multiple values where dates match
;WITH ByDate
AS (
SELECT UserId, Value, RANK() OVER (PARTITION BY UserId ORDER BY Date DESC) Rnk
FROM UserDates
)
SELECT UserId, Value
FROM ByDate
WHERE Rnk = 1
小开

我没有Oracle来测试它，但最有效的解决方案是使用分析查询。它应该看起来像这样:

SELECT DISTINCT
UserId
, MaxValue
FROM (
SELECT UserId
, FIRST (Value) Over (
PARTITION BY UserId
ORDER BY Date DESC
) MaxValue
FROM SomeTable
)

我怀疑您可以摆脱外部查询，并在内部放置distinct，但我不确定。与此同时，我知道这招管用。

如果您想学习分析查询，我建议您阅读http://www.orafaq.com/node/55和http://www.akadia.com/services/ora_analytic_functions.html。这是一个简短的总结。

在底层，分析查询对整个数据集进行排序，然后按顺序进行处理。当你处理它的时候，你根据特定的标准对数据集进行分区，然后对每一行查看一些窗口(默认为当前行分区中的第一个值-默认也是最有效的)，并可以使用许多分析函数(其列表非常类似于聚合函数)计算值。

在本例中，下面是内部查询的功能。整个数据集先按UserId排序，再按Date DESC排序，然后一次处理。对于每一行，您返回UserId和该UserId看到的第一个日期(因为日期是按DESC排序的，所以这是最大日期)。这就得到了重复行的答案。然后外部的DISTINCT压缩重复项。

这并不是一个特别引人注目的分析查询示例。如果想获得更大的胜利，可以考虑制作一张财务收据表格，计算每个用户和收据的总花费。分析查询可以有效地解决这个问题。其他解决方案效率较低。这就是为什么它们是2003 SQL标准的一部分。(不幸的是Postgres还没有。叽阿…)

小开

我看到许多人使用子查询或其他窗口函数来执行此操作，但我经常以以下方式执行这种不带子查询的查询。它使用普通的标准SQL，因此它应该适用于任何品牌的RDBMS。

SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON (t1.UserId = t2.UserId AND t1."Date" < t2."Date")
WHERE t2.UserId IS NULL;

换句话说:在没有其他具有相同的UserId且日期更大的行存在的情况下，从t1中获取行。

(我把标识符"日期"分隔符，因为它是一个SQL保留字。)

如果t1."Date" = t2."Date"，加倍出现。通常表有auto_inc(seq)键，例如id。 

SELECT t1.*
FROM mytable t1
LEFT OUTER JOIN mytable t2
ON t1.UserId = t2.UserId AND ((t1."Date" < t2."Date")
OR (t1."Date" = t2."Date" AND t1.id < t2.id))
WHERE t2.UserId IS NULL;

关于@Farhan的评论:

下面是更详细的解释:

外部连接尝试连接t1和t2。默认情况下，返回t1的所有结果，并且如果在t2中有匹配项，它也被返回。如果t1的给定行在t2中没有匹配，那么查询仍然返回t1的行，并使用NULL作为t2的所有列的占位符。这就是外层连接的工作原理。

这个查询中的技巧是设计连接的匹配条件，这样t2必须匹配userid4 userid和userid5 date。这个想法是，如果在t2中存在一行，它有更大的date，那么在t1中与userid6中相比，它是userid中最大的date。但是如果没有匹配——也就是说，如果在t2中不存在date大于t1的行——我们就知道在给定的userid中，t1中的行是date最大的行。

在这些情况下(当没有匹配时)，t2的列将是NULL——即使是连接条件中指定的列。这就是为什么我们要使用WHERE t2.UserId IS NULL，因为我们正在寻找对于给定的userid，没有发现更大的date行的情况。

小开

由于不工作，我手头没有Oracle，但我似乎记得Oracle允许在一个in子句中匹配多个列，这至少应该避免使用相关子查询的选项，这很少是一个好主意。

可能是这样的(不记得列列表是否应该加括号):

SELECT *
FROM MyTable
WHERE (User, Date) IN
( SELECT User, MAX(Date) FROM MyTable GROUP BY User)

编辑:刚刚真的试了一下:

SQL> create table MyTable (usr char(1), dt date);
SQL> insert into mytable values ('A','01-JAN-2009');
SQL> insert into mytable values ('B','01-JAN-2009');
SQL> insert into mytable values ('A', '31-DEC-2008');
SQL> insert into mytable values ('B', '31-DEC-2008');
SQL> select usr, dt from mytable
2  where (usr, dt) in
3  ( select usr, max(dt) from mytable group by usr)
4  /


U DT
- ---------
A 01-JAN-09
B 01-JAN-09

所以它是有效的，尽管其他地方提到的一些新东西可能性能更好。

小开

这也会处理重复的数据(为每个user_id返回一行):

SELECT *
FROM (
SELECT u.*, FIRST_VALUE(u.rowid) OVER(PARTITION BY u.user_id ORDER BY u.date DESC) AS last_rowid
FROM users u
) u2
WHERE u2.rowid = u2.last_rowid
小开

答案是Oracle。这里有一个更复杂的SQL回答:

谁的整体作业成绩最好(作业点数最多)?

SELECT FIRST, LAST, SUM(POINTS) AS TOTAL
FROM STUDENTS S, RESULTS R
WHERE S.SID = R.SID AND R.CAT = 'H'
GROUP BY S.SID, FIRST, LAST
HAVING SUM(POINTS) >= ALL (SELECT SUM (POINTS)
FROM RESULTS
WHERE CAT = 'H'
GROUP BY SID)

还有一个更难的例子，需要一些解释，我没有时间了

给出2008年最受欢迎的书(ISBN和书名)，即2008年最常被借阅的书。

SELECT X.ISBN, X.title, X.loans
FROM (SELECT Book.ISBN, Book.title, count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan
ON Copy.copyId = Loan.copyId
GROUP BY Book.title) X
HAVING loans >= ALL (SELECT count(Loan.dateTimeOut) AS loans
FROM CatalogEntry Book
LEFT JOIN BookOnShelf Copy
ON Book.bookId = Copy.bookId
LEFT JOIN (SELECT * FROM Loan WHERE YEAR(Loan.dateTimeOut) = 2008) Loan
ON Copy.copyId = Loan.copyId
GROUP BY Book.title);

希望这能对(任何人)有所帮助。：）

< p >问候, 古< / p >
小开

刚刚测试了这个，它似乎在日志记录表上工作

select ColumnNames, max(DateColumn) from log  group by ColumnNames order by 1 desc
小开

只是需要在工作中写一个“活”的例子:)

它支持UserId在相同日期上的多个值。

< p >列: 用户id，值，日期

.使用实例 
SELECT
DISTINCT UserId,
MAX(Date) OVER (PARTITION BY UserId ORDER BY Date DESC),
MAX(Values) OVER (PARTITION BY UserId ORDER BY Date DESC)
FROM
(
SELECT UserId, Date, SUM(Value) As Values
FROM <<table_name>>
GROUP BY UserId, Date
)

您可以使用FIRST_VALUE而不是MAX，并在解释计划中查找它。我没有时间玩它。

当然，如果搜索巨大的表，在查询中使用FULL提示可能会更好。

小开

难道一个qualified子句不是既简单又最好吗?

select userid, my_date, ...
from users
qualify rank() over (partition by userid order by my_date desc) = 1

对于上下文，在Teradata这里一个像样的大小测试运行在17秒与这个合格版本和在23秒与“内联视图”/Aldridge解决方案#1。

小开

在PostgreSQL 8.4或更高版本中，你可以这样使用:

select user_id, user_value_1, user_value_2
from (select user_id, user_value_1, user_value_2, row_number()
over (partition by user_id order by user_date desc)
from users) as r
where r.row_number=1
小开 
select VALUE from TABLE1 where TIME =
(select max(TIME) from TABLE1 where DATE=
(select max(DATE) from TABLE1 where CRITERIA=CRITERIA))
小开 
select   UserId,max(Date) over (partition by UserId) value from users;
小开

MySQL没有分区KEEP, DENSE_RANK概念的解决方案。

select userid,
my_date,
...
from
(
select @sno:= case when @pid<>userid then 0
else @sno+1
end as serialnumber,
@pid:=userid,
my_Date,
...
from   users order by userid, my_date
) a
where a.serialnumber=0

参考:# EYZ0

小开

我已经很晚了，但下面的黑客将超越相关子查询和任何分析功能，但有一个限制:值必须转换为字符串。所以它适用于日期，数字和其他字符串。代码看起来不太好，但执行配置文件很棒。

select
userid,
to_number(substr(max(to_char(date,'yyyymmdd') || to_char(value)), 9)) as value,
max(date) as date
from
users
group by
userid

这段代码运行良好的原因是它只需要扫描表一次。它不需要任何索引，最重要的是，它不需要像大多数分析函数那样对表进行排序。如果您需要为单个用户id过滤结果，索引将有所帮助。

小开

如果你在使用Postgres，你可以使用array_agg

SELECT userid,MAX(adate),(array_agg(value ORDER BY adate DESC))[1] as value
FROM YOURTABLE
GROUP BY userid

我不熟悉甲骨文。这是我想到的

SELECT
userid,
MAX(adate),
SUBSTR(
(LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)),
0,
INSTR((LISTAGG(value, ',') WITHIN GROUP (ORDER BY adate DESC)), ',')-1
) as value
FROM YOURTABLE
GROUP BY userid

两个查询返回的结果都与接受的答案相同。看到SQLFiddles:

  1. < a href = " http://sqlfiddle.com/ !4/2749b5/42" rel="nofollow">接受答案 .
  2. < a href = " http://sqlfiddle.com/ !12/24a7a/18" rel="nofollow">我的解决方案与Postgres
  3. < a href = " http://sqlfiddle.com/ !4/2749b5/41" rel="nofollow">我的解决方案与Oracle
小开

检查这个链接，如果你的问题似乎与该页相似，那么我会建议你以下查询，这将为该链接提供解决方案

select distinct sno,item_name,max(start_date) over(sno分区)，max(end_date) over(sno分区)，max(creation_date) over(sno分区)， Max (last_modified_date) over(sno分区) 从uniq_select_records

. sno,item_name asc

是否会给出与该链接相关的准确结果

小开

在Oracle 12c+中，你可以使用前n查询和分析函数rank来实现这个非常简洁的没有子查询:

select *
from your_table
order by rank() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;

上面的代码返回每个用户my_date最大的所有行。

如果你只想要有最大日期的一行，那么将rank替换为row_number:

select *
from your_table
order by row_number() over (partition by user_id order by my_date desc)
fetch first 1 row with ties;
小开

使用代码:

select T.UserId,T.dt from (select UserId,max(dt)
over (partition by UserId) as dt from t_users)T where T.dt=dt;
这将检索结果，不管UserId的重复值。 如果你的UserId是唯一的，那么它就变得更简单:

select UserId,max(dt) from t_users group by UserId;
小开 
SELECT a.*
FROM user a INNER JOIN (SELECT userid,Max(date) AS date12 FROM user1 GROUP BY userid) b
ON a.date=b.date12 AND a.userid=b.userid ORDER BY a.userid;
小开

使用ROW_NUMBER()为每个UserId在降序Date上分配一个唯一的排名，然后为每个UserId过滤到第一行(即ROW_NUMBER = 1)。

SELECT UserId, Value, Date
FROM (SELECT UserId, Value, Date,
ROW_NUMBER() OVER (PARTITION BY UserId ORDER BY Date DESC) rn
FROM users) u
WHERE rn = 1;
小开 
SELECT a.userid,a.values1,b.mm
FROM table_name a,(SELECT userid,Max(date1)AS mm FROM table_name GROUP BY userid) b
WHERE a.userid=b.userid AND a.DATE1=b.mm;
小开

以下查询可以工作:

SELECT user_id, value, date , row_number() OVER (PARTITION BY user_id ORDER BY date desc) AS rn
FROM table_name
WHERE rn= 1

提交答案