SQL选择第一列,并为每一行选择唯一的ID和最后日期 [英] SQL Select First column and for each row select unique ID and the last date
本文介绍了SQL选择第一列,并为每一行选择唯一的ID和最后日期的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我在早上遇到问题,我尝试了许多解决方案,但没有任何结果给我带来预期的结果.
I have a problems this mornig , I have tried many solutions and nothing gave me the expected result.
我有一个像这样的表:
+----+----------+-------+
| ID | COL2 | DATE |
+----+----------+-------+
| 1 | 1 | 2001 |
| 1 | 2 | 2002 |
| 1 | 3 | 2003 |
| 1 | 4 | 2004 |
| 2 | 1 | 2001 |
| 2 | 2 | 2002 |
| 2 | 3 | 2003 |
| 2 | 4 | 2004 |
+----+----------+-------+
我有一个查询,返回的结果是这样的: 我有唯一的ID,对于该ID,我想获取ID的最后日期
And I have a query that returns a result like this : I have the unique ID and for this ID I want to take the last date of the ID
+----+----------+-------+
| ID | COL2 | DATE |
+----+----------+-------+
| 1 | 4 | 2004 |
| 2 | 4 | 2004 |
+----+----------+-------+
但是我不知道该怎么做. 我尝试了加入,交叉应用..
But I don't have any idea how I can do that. I tried Join , CROSS APPLY ..
如果您有想法,
谢谢
克莱门特·法亚德
推荐答案
declare @t table (ID INT,Col2 INT,Date INT)
insert into @t(ID,Col2,Date)values (1,1,2001)
insert into @t(ID,Col2,Date)values (1,2,2001)
insert into @t(ID,Col2,Date)values (1,3,2001)
insert into @t(ID,Col2,Date)values (1,4,2001)
insert into @t(ID,Col2,Date)values (2,1,2002)
insert into @t(ID,Col2,Date)values (2,2,2002)
insert into @t(ID,Col2,Date)values (2,3,2002)
insert into @t(ID,Col2,Date)values (2,4,2002)
;with cte as(
select
*,
rn = row_number() over(partition by ID order by Col2 desc)
from @t
)
select
ID,
Col2,
Date
from cte
where
rn = 1
这篇关于SQL选择第一列,并为每一行选择唯一的ID和最后日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文