问题描述
我有一种情况,我希望通过获取SQL Server中的最大记录来返回3列.像下面一样
I have a scenario where I would love to return 3 columns by getting the max record in SQL Server. like below
ID PID PDATE Satus CANUMBERS
1 PS1 2015-08-05 12:20:49.627 0 1 CAA0322
2 PS2 2015-08-06 04:05:52.053 0 1 CAA0322
3 PS2 2015-08-07 07:17:32.653 1 1 CAA0322
在这种情况下,要查找的是每个PID
的最大值PDate
,就像一直输入的pid的最后一条记录一样.
in this case what am looking for is getting max PDate
of each PID
, like the last record of pid entered at all times.
我尝试了以下操作:
SELECT
MAX(ID), PID, PDATE
FROM
TABLE1
GROUP BY
PID, PDATE ...
将pdate分组的问题,它返回所有记录.我只想要
The problem with grouping the pdate, it returns all the records. i only want the max pdate for each PID like
1 PS1 2015-08-05 12:20:49.627 0 1 CAA0322
3 PS2 2015-08-07 07:17:32.653 1 1 CAA0322
请帮忙
推荐答案
您需要将CTE与ROW_NUMBER()
窗口功能一起使用-类似于这样:
You need to use a CTE with a ROW_NUMBER()
windowing function - something like this:
;WITH CTE AS
(
SELECT
ID, PID, PDATE, Satus, CANUMERS,
RowNum = ROW_NUMBER() OVER (PARTITION BY PID ORDER BY PDATE DESC)
FROM
dbo.YourTable
)
SELECT
ID, PID, PDATE, Satus, CANUMERS
FROM
CTE
WHERE
RowNum = 1
PARTITION BY
子句按列PID
对数据进行分区,然后对每个分区从1开始进行编号-因此,具有RowNum = 1
的行是最近行(按降序-最新的第一个),每个PID
The PARTITION BY
clause partitions your data by the column PID
and then numbers each partition beginning from 1 - so the row with RowNum = 1
is the most recent row (ordered by PDATE
descending - newest first) for each PID
这篇关于在T-SQL中获取最大记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!