问题描述
我有一个表,其中包含有关MSSQL数据库中一系列事件的数据:
I have a table which contains data on a series of events in an MSSQL database:
ID Name Date Location Owner
--- --------------------------------------------------------- ----------- -------------------------------- -----------
1 Seminar Name 1 2013-08-08 A Location Name 16
2 Another Event Name 2013-07-30 Another Location 18
3 Event Title 2013-08-21 Head Office 94
4 Another Title 2013-08-30 London Office 18
5 Seminar Name 2 2013-08-27 Town Hall 19
6 Title 2013-08-20 Somewhere Else 196
7 Fake Seminar For Testing 2013-08-25 Fake Location 196
希望您可以看到该表包含许多事件,这些事件由我们的应用程序中的多个用户拥有.我试图找出是否存在可以用于为每个用户选择最近发生的事件的查询.我认为显示我想要的最简单方法是显示我正在寻找的理想结果表(基于今天的日期):
Hopefully you can see that this table contains a number of events which are owned by several users in our application. I am trying to figure out if there is a query I can use to select the most recently occurring event for each user. I think the easiest way to show what I want is to show the ideal result table I'm looking for (based on today's date):
ID Name Date Location Owner
--- --------------------------------------------------------- ----------- -------------------------------- -----------
1 Seminar Name 1 2013-08-08 A Location Name 16
2 Another Event Name 2013-07-30 Another Location 18
3 Event Title 2013-08-21 Head Office 94
5 Seminar Name 2 2013-08-27 Town Hall 19
6 Title 2013-08-20 Somewhere Else 196
此刻我能想到的最好的方法是:
The best I could come up with at the moment is this query:
SELECT DISTINCT Owner, Date, ID FROM Seminars
GROUP BY Owner, Date, ID ORDER BY Date
它并没有真正做我想做的事,我认为真正的解决方案会比这复杂一些,因为我也需要根据今天的日期进行选择.
It doesn't really do what I want to do and I think the real solution is going to be a bit more complex than this as I need to somehow select based to today's date too.
推荐答案
WITH CTE
AS
(
SELECT *,
ROW_NUMBER() OVER(PARTITION BY Owner
ORDER BY Date DESC) AS RN
FROM tablename
)
SELECT ID, Name, Date, Location, Owner
FROM CTE
WHERE RN = 1;
这篇关于如何基于多个字段选择不同的行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!