给定一个名为Messages的数据库表,该表具有以下列:-
MessageID, FromUser, ToUser, Message, DateTime
我想编写一个SQL查询,为"FromUser"列选择不同的值,但我也希望这些值按DateTime排序。
本质上,一个查询,例如:
SELECT * FROM Messages WHERE ToUser='1' ORDER BY DateTime DESC
然后,选择不同的FromUser,例如:
SELECT DISTINCT FromUser FROM Messages WHERE ToUser='1'
同时,维护上一个查询的顺序。
我尝试过使用嵌套查询,但遇到了一个问题,因为您无法在内部查询中使用ORDERBY。
本质上,我希望查询的语法表示(这是无效的,但…):
SELECT DISTINCT FromUser FROM Messages WHERE ToUser=1 AND FromUser IN
(SELECT * FROM Messages WHERE ToUser=1 ORDER BY DateTime DESC)
我猜这就是您所需要的:
;WITH CTE AS
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY [FromUser] ORDER BY [DateTime] DESC) RN
FROM [Messages]
WHERE ToUser='1'
)
SELECT *
FROM CTE
WHERE RN = 1
因此,在select
语句中包含DateTime
,可以按其分组,也可以使用聚合函数之一,如MAX()
,如:
Select fromuser, datetime
from messages
where touser = '1'
group by
fromuser, datetime
order by
fromuser, datetime
或
Select fromuser, max(datetime) as max_datetime
from messages
where touser = '1'
group by
fromuser
每个FromUser都有多个时间戳。我将假设您希望为它们中的每一个使用最后的时间戳。
select *
from Messages join
(select fromuser, MAX(datetime) as lastdate from Messages
group by FromUser) as abb1 on abb1.FromUser = messages.FromUser and abb1.lastdate = messages.Datetime
where ToUser = '1'
order by Datetime desc
SELECT DISTINCT [Messages].[FromUser] FROM (
SELECT [FromUser] as [FromUser], MAX(DateTime) as [Date]
FROM [Messages]
GROUP BY [FromUser]) as [Messages]
另一种方式;
select * from
(
select *, max(DateTime) over (partition by FromUser) newest
from Messages
) T
where DateTime = newest
(如果同一个人有相同的日期,则为n行)