根据列中多个组合的日期选择最近一行 [英] Select most recent row based on date where mutiple combinations in columns
问题描述
嗨大师，
我有一张包含以下数据样本的表格。 A栏和A栏B& C是整数Mdate是Date。我需要根据日期和时间选择所有行。 C栏
I have a table with the following sample of data. Column A & B & C are integer Mdate is Date. I need to select all rows based on date & columns C
A B         C          Mdate
A B C Mdate
1 0        1         11-07-2017
1 0 1 11-07-2017
1 202      1         15-05-2019
1 202 1 15-05-2019
1 2449    2         15-05-2019
1 2449 2 15-05-2019
1960 2018    1         23-04-2018
1960 2018 1 23-04-2018
1960 2018    2         23-04-2018
1960 2018 2 23-04-2018
2448 4          2         07-11-2018
2448 4 2 07-11-2018
0 2018    1         10-03-2019
0 2018 1 10-03-2019
0 2018    2         10-03-2019
0 2018 2 10-03-2019
我需要得到的结果如下所示
The result I need to get would look like this
A ;   B         C          Mdate
A B C Mdate
1 202      1         15-05-2019
1 202 1 15-05-2019
1 2449    2         15-05-2019
1 2449 2 15-05-2019
2448 4          2         07-11-2018
2448 4 2 07-11-2018
0 2018    1         10-03-2019
0 2018 1 10-03-2019
0 2018    2         10-03-2019
0 2018 2 10-03-2019
Col A& A中的配对1 Col B中的0比Col A&中的1的配对早。 Col Col 202和1960年Col A& amp; Col B中的2018年比Col A& A中的0配对年龄大。 2018年在Col B中。这些行中的每一行都在
的重复值中与同一列和&必须排除最旧的那些。
The pairing1 in Col A & 0 in Col B is older than the pairing of 1 in Col A & 202 in Col B and the pairing of 1960 in Col A & 2018 in Col B is older than the pairing of 0 in Col A & 2018 in Col B. Each of those rows has a repeated value in
the same column & the oldest of those must be excluded.
我正在尝试编写一个查询来实现这一点。到目前为止我有这个
I'm trying to write a query to achieve this. So far I have this
WITH CTE
As
(
SELECT A, B, C, Mdate,
ROW_NUMBER() OVER (PARTITION BY B, C ORDER BY Mdate DESC) As M
FROM tblTestData
)
SELECT *
FROM CTE
WHERE M = 1
ORDER BY A, B, C
按B列分区。我需要在A列和A列上做同样的事情。按照我的例子来获得结果。我有什么想法可以达到这个目的吗？非常感谢任何指导。
which partitions by Column B. I need to do the same on column A & group to get the result as per my example. Any ideas how I can achieve this? Any guidance much appreciated.
推荐答案
您是否为您发布了CREATE TABLE语句表和样本数据作为INSERT语句，我会测试。现在我有机会这样做：
Had you posted the CREATE TABLE statement for you table and the sample data as INSERT statements, I would have tested. Now I'm taking chances that this will work:
WITH CTE1 AS（
SELECT A，B，C，Mdate，
ROW_NUMBER（）OVER（由Mdate DESC订购的分段）由于M b
FROM tblTestData），CTE2 AS（
SELECT A，B，C，Mdate，
ROW_NUMBER（）OVER（按B顺序划分Mdate DESC ）由于M¥
FROM tblTestData）
SELECT A，B，C，Mdate
FROM CTE1
WHERE M = 1
INTERSECT
选择A，B，C，Mdate
FROM CTE2
WHERE M = 1
WITH CTE1 AS (
SELECT A, B, C, Mdate,
ROW_NUMBER() OVER (PARTITION BY A ORDER BY Mdate DESC) As M
FROM tblTestData ), CTE2 AS (
SELECT A, B, C, Mdate,
ROW_NUMBER() OVER (PARTITION BY B ORDER BY Mdate DESC) As M
FROM tblTestData )
SELECT A, B, C, Mdate
FROM CTE1
WHERE M = 1
INTERSECT
SELECT A, B, C, Mdate
FROM CTE2
WHERE M = 1
这篇关于根据列中多个组合的日期选择最近一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
