как сделать SQL запрос на последнюю запись для каждого пользователя
У меня есть таблица, которая является коллекцией записей о том, когда пользователь вошел в систему.
username, date, value
--------------------------
brad, 1/2/2010, 1.1
fred, 1/3/2010, 1.0
bob, 8/4/2009, 1.5
brad, 2/2/2010, 1.2
fred, 12/2/2009, 1.3
etc..
Как создать запрос, который дал бы мне последнюю дату для каждого пользователя?
обновление: Я забыл, что мне нужно, чтобы иметь значение, которое идет вместе с последней датой.
21 ответ:
select t.username, t.date, t.value from MyTable t inner join ( select username, max(date) as MaxDate from MyTable group by username ) tm on t.username = tm.username and t.date = tm.MaxDate
использование оконных функций (работает в Oracle, Postgres 8.4, SQL Server 2005, DB2, Sybase, Firebird 3.0, MariaDB 10.3)
select * from ( select username, date, value, row_number() over(partition by username order by date desc) as rn from yourtable ) t where t.rn = 1
Я вижу, что большинство разработчиков используют встроенный запрос, не учитывая его влияние на огромные данные.
просто, вы можете достичь этого с помощью:
SELECT a.username, a.date, a.value FROM myTable a LEFT OUTER JOIN myTable b ON a.username = b.username AND a.date < b.date WHERE b.username IS NULL ORDER BY a.date desc;
чтобы получить всю строку, содержащую максимальный срок для пользователя:
select username, date, value from tablename where (username, date) in ( select username, max(date) as date from tablename group by username )
SELECT * FROM MyTable T1 WHERE date = ( SELECT max(date) FROM MyTable T2 WHERE T1.username=T2.username )
Это должно дать вам правильный результат для вашего отредактированы вопрос.
подзапрос обязательно найдет только строки с последней датой, а внешний
GROUP BY
будет заботиться о связи. Когда есть две записи на одну и ту же дату для одного и того же пользователя, он вернет один с самым высокимvalue
.SELECT t.username, t.date, MAX( t.value ) value FROM your_table t JOIN ( SELECT username, MAX( date ) date FROM your_table GROUP BY username ) x ON ( x.username = t.username AND x.date = t.date ) GROUP BY t.username, t.date
для Oracle сортирует результирующий набор в убывающем порядке и берет первую запись, так что вы получите последнюю запись:
select * from mytable where rownum = 1 order by date desc
SELECT Username, date, value from MyTable mt inner join (select username, max(date) date from MyTable group by username) sub on sub.username = mt.username and sub.date = mt.date
решить проблему. Это может не так хорошо работать на больших таблицах, даже с хорошей индексацией.
SELECT * FROM ReportStatus c inner join ( SELECT MAX(Date) AS MaxDate FROM ReportStatus ) m on c.date = m.maxdate
SELECT t1.username, t1.date, value FROM MyTable as t1 INNER JOIN (SELECT username, MAX(date) FROM MyTable GROUP BY username) as t2 ON t2.username = t1.username AND t2.date = t1.date
Select * from table1 where lastest_date=(select Max(latest_date) from table1 where user=yourUserName)
внутренний запрос вернет последнюю дату для текущего пользователя, внешний запрос будет тянуть все данные в соответствии с результатом внутреннего запроса.
я использовал этот способ, чтобы взять последнюю запись для каждого пользователя, который у меня есть на моем столе. Это был запрос, чтобы получить последнее место для продавца в соответствии с недавним временем, обнаруженным на устройствах PDA.
CREATE FUNCTION dbo.UsersLocation() RETURNS TABLE AS RETURN Select GS.UserID, MAX(GS.UTCDateTime) 'LastDate' From USERGPS GS where year(GS.UTCDateTime) = YEAR(GETDATE()) Group By GS.UserID GO select gs.UserID, sl.LastDate, gs.Latitude , gs.Longitude from USERGPS gs inner join USER s on gs.SalesManNo = s.SalesmanNo inner join dbo.UsersLocation() sl on gs.UserID= sl.UserID and gs.UTCDateTime = sl.LastDate order by LastDate desc
моя небольшая компиляция
- self
join
лучше, чем вложенныеselect
- но
group by
не даетprimary key
что предпочтительнее дляjoin
- этот ключ может быть дано
partition by
в сочетании сfirst_value
(docs)Итак, вот запрос:
select t.* from Table t inner join ( select distinct first_value(ID) over(partition by GroupColumn order by DateColumn desc) as ID from Table where FilterColumn = 'value' ) j on t.ID = j.IDплюсы:
- фильтровать данные с помощью
where
оператор с использованием любого столбцаselect
любой столбцы из отфильтрованных строкплюсы:
- нужен MS SQL Server, начиная с 2012 года.
Я сделал несколько для моего приложения, как это:
Ниже приведен запрос:
select distinct i.userId,i.statusCheck, l.userName from internetstatus as i inner join login as l on i.userID=l.userID where nowtime in((select max(nowtime) from InternetStatus group by userID));
из моего опыта самый быстрый способ-взять каждую строку, для которой нет новой строки в таблице. Вот небольшой тест с некоторыми данными, которые у меня есть под рукой.
еще одно преимущество заключается в том, что используемый синтаксис очень прост, и что значение запроса довольно легко понять (возьмите все строки, чтобы не было новой строки для рассматриваемого имени пользователя).
НЕ СУЩЕСТВУЕТ
SELECT username, value FROM t WHERE NOT EXISTS ( SELECT * FROM t AS witness WHERE witness.date > t.date );
объясните общую стоимость : 2.38136
функции row_number
SELECT username, value FROM ( SELECT username, value, row_number() OVER (PARTITION BY username ORDER BY date DESC) AS rn FROM t ) t2 WHERE rn = 1
общая стоимость : 61.5823
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
SELECT t.username, t.value FROM t INNER JOIN ( SELECT username, MAX(date) AS date FROM t GROUP BY username ) tm ON t.username = tm.username AND t.date = tm.date;
объясните общую стоимость: 67.5439
ЛЕВОЕ ВНЕШНЕЕ СОЕДИНЕНИЕ
SELECT username, value FROM t LEFT OUTER JOIN t AS w ON t.username = w.username AND t.date < w.date WHERE w.username IS NULL
объясните общую стоимость: 62.964
объясните планы приходят из базы данных с около 10k строк, хранящихся в виде XML. Используемые запросы также содержат предикат "group_id = '1'".
это похоже на один из ответов выше, но на мой взгляд это намного проще и аккуратнее. Кроме того, показывает хорошее использование для оператора cross apply. Для SQL Server 2005 и выше...
select a.username, a.date, a.value, from yourtable a cross apply (select max(date) 'maxdate' from yourtable a1 where a.username=a1.username) b where a.date=b.maxdate
вы также можете использовать аналитическую функцию ранга
with temp as ( select username, date, RANK() over (partition by username order by date desc) as rnk from t ) select username, rnk from t where rnk = 1
вы бы использовали агрегатную функцию MAX и GROUP BY
SELECT username, MAX(date), value FROM tablename GROUP BY username, value