问题描述
a = id
b = date
c = NewestDate
对于每个 a,我有 b c
For every a, I have b c
如何获取每组 ID 的最新日期(我的a"列)?它们并非都具有相同的结束日期.所以我确实需要一个最大值.
How can I get the newest date for every set of id's (MY 'a' column)? they don't all have the same end date. So I do need a max for all of them.
数据在myTable中,到目前为止,我尝试过:
The data is in myTable,so far i tried:
select *
into #myTable
from myTable
select
t.a
,t.b
,t.c
,(select max(b) from myTable) as c
from myTable t
left join #myTable t1
on t.a = t1.a and t.b = t1.b
order by a, b
上面代码的问题是在'c'中放置了它们的最大日期,这不是我真正想要的.
The problem with the above code is that in 'c' it is placed the max date of them all, which is not what I actually want.
问题现在已通过 Dmitry Poliakov 给出的答案解决(谢谢).使用:
the problem is now solved with the answer given by Dmitry Poliakov (thanks). Used:
SELECT a,
b,
max(b) OVER (PARTITION BY a) AS c
FROM myTable
ORDER BY a,b
推荐答案
可以为每组id选择最大日期
you can select maximum date for each group of ids as
SELECT a,
b,
max(b) OVER (PARTITION BY a) AS c
FROM myTable
ORDER BY a,b
问题的第二(编辑)部分的可能解决方案之一是
one of possible solutions for the second(edited) part of question is
WITH cte AS (
SELECT a,
b,
max(b) OVER (PARTITION BY a) AS c,
d
FROM myTable
)
SELECT t1.a,
t1.b,
t1.c,
t1.d,
t2.d AS e
FROM cte t1
JOIN cte t2 ON t1.a=t2.a AND t1.c=t2.b
这篇关于每个 id 的最近(最大)日期的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!