使用CTE获取每个组的最后一行 [英] Get last row of each group with CTE

问题描述

我有这样的查询:

  DECLARE @DeadDesigns AS TABLE(LegacyKey INT，DesignKey INT，状态键INT，DesignGroupId UNIQUEIDENTIFIER)插入@DeadDesigns从项目AS P中选择[P].[LegacyKey]，[D] .DesignKey，[D].[StatusKey]，[D].[DesignGroupId]内联接DesignGroup AS DG ON P.ProjectKey = DG.ProjectKeyDG.DesignGroupId = D.DesignGroupId的内部联接设计在[D].[StatusKey] = 67 --DEAD的位置;与CTE2(LegacyKey，DesignKey，StatusKey，DesignGroupId，RN)AS(SELECT LegacyKey，DesignKey，StatusKey，DesignGroupId，ROW_NUMBER()超过(PARTITION由[LegacyKey]，[DesignGroupId]或ORDER BY [DesignGroupId])作为RN来自@DeadDesignsGROUP BY [DesignGroupId]，[LegacyKey]，DesignKey，StatusKey)选择*从CTE2 

结构为:

一个 LegacyKey 可以具有多个 DesignGroupId ，一个 DesignGroupId 可以具有多个 DesignKey

目标是获取与项目分开的每个 DesignGroupId 的最后一个 DesignKey

错误的结果:

  + ----------- + ----------- + ----------- + -------------------------------------- + ---- +|LegacyKey |DesignKey |状态KEy |DesignGroupId |RN |+ ----------- + ----------- + ----------- + -------------------------------------- + ---- +|14002 |2416 |67 |1A07C80E-E5E2-45F0-A5D2-419BAF3DC106 |1 ||14002 |2819 |70 |1A07C80E-E5E2-45F0-A5D2-419BAF3DC106 |2 |+ ----------- + ----------- + ----------- + -------------------------------------- + ---- + 

这是错误的，因为如果DesignGroupId是不同的结果是正确的，则它是相同的DesignGroupId.

详细的欲望结果:

我想用 DesignGroupId 分隔它，所以如果有两个不同的 DesignGroupId 并且它是相同的 LegacyKey RN 如果我们有3个 DesignGroupId 但第二行的相同 LegacyKey RN ，则第二行的应该为3，依此类推.最后，我想获取每个 LegacyKey

中每个 DesignGroupId 中的所有最后一个 DesignKey

我在做什么错了?

另一个例子

  + ----------- + ----------- + ----------- + -------------------------------------- + ---- +|LegacyKey |DesignKey |状态KEy |DesignGroupId |RN |+ ----------- + ----------- + ----------- + -------------------------------------- + ---- +|18288 |3974 |63 |F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |1 ||18288 |4096 |107 |F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |2 ||18288 |7224 |66 |F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |3 ||18288 |4842 |66 |A18320D8-C7A8-4A2A-AFDE-38A483C24E81 |1 ||18289 |7325 |66 |90C224D9-2514-4294-8DEC-D3EE16EC2D00 |1 ||18289 |3975 |63 |90C224D9-2514-4294-8DEC-D3EE16EC2D00 |2 |+ ----------- + ----------- + ----------- + -------------------------------------- + ---- + 

在这种情况下，我有两个不同的 LegacyKey ，但结果是错误的，因为它返回了同一 DesignGroupId 的所有 RN ，而我只想要最后一个.期望的结果是:

  + ----------- + ----------- + ----------- + -------------------------------------- + ---- +|LegacyKey |DesignKey |状态KEy |DesignGroupId |RN |+ ----------- + ----------- + ----------- + -------------------------------------- + ---- +|18288 |7224 |66 |F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |3 ||18288 |4842 |66 |A18320D8-C7A8-4A2A-AFDE-38A483C24E81 |1 ||18289 |3975 |63 |90C224D9-2514-4294-8DEC-D3EE16EC2D00 |2 |+ ----------- + ----------- + ----------- + -------------------------------------- + ---- + 

您可以看到我得到了 DesignGroupId 的最后一个 RN ，但是它们位于相同的 LegacyKey

解决方案

我不希望子查询中出现 GROUP BY .我希望在外部查询中进行过滤:

 与CTE2(LegacyKey，DesignKey，StatusKey，DesignGroupId，RN)AS(SELECT LegacyKey，DesignKey，StatusKey，DesignGroupId，ROW_NUMBER()超过(按[LegacyKey]，[DesignGroupId]，[DesignGroupId] DESC排序的序列)AS序列号来自@DeadDesigns)选择 *来自CTE2WHERE seqnum = 1; 

请注意，我已将 ORDER BY 更改为 DESC ，这在您需要最后"或最新"的东西时很常见.

I have a query like this:

DECLARE @DeadDesigns AS TABLE(
 LegacyKey INT
,DesignKey INT
,StatusKey INT
,DesignGroupId UNIQUEIDENTIFIER
)
INSERT INTO @DeadDesigns
SELECT [P].[LegacyKey],[D].DesignKey, [D].[StatusKey], [D].[DesignGroupId] FROM Project AS P
INNER JOIN DesignGroup AS DG ON P.ProjectKey = DG.ProjectKey
INNER JOIN Design AS D ON DG.DesignGroupId  = D.DesignGroupId
WHERE [D].[StatusKey] = 67 --DEAD 

;WITH CTE2 (LegacyKey, DesignKey, StatusKey, DesignGroupId, RN) AS (
SELECT LegacyKey, DesignKey, StatusKey, DesignGroupId,
ROW_NUMBER() OVER (PARTITION BY [LegacyKey], [DesignGroupId] ORDER BY [DesignGroupId]) AS RN
FROM @DeadDesigns 
    GROUP BY [DesignGroupId],[LegacyKey],DesignKey,StatusKey
)
SELECT * FROM CTE2

Structure is:

One LegacyKey can have multiple DesignGroupId and one DesignGroupId can have multiple DesignKey

Objective is to get last DesignKey of each DesignGroupId separated from project

Wrong result:

+-----------+-----------+-----------+--------------------------------------+----+
| LegacyKey | DesignKey | StatusKEy |            DesignGroupId             | RN |
+-----------+-----------+-----------+--------------------------------------+----+
|     14002 |      2416 |        67 | 1A07C80E-E5E2-45F0-A5D2-419BAF3DC106 |  1 |
|     14002 |      2819 |        70 | 1A07C80E-E5E2-45F0-A5D2-419BAF3DC106 |  2 |
+-----------+-----------+-----------+--------------------------------------+----+

This is wrong because it is the same DesignGroupId, if that DesignGroupId was different result was correct.

Detailed desire result:

I want to separate it by DesignGroupId so if have two different DesignGroupId and it's the same LegacyKey RN of second row should be 2.. if we have 3 DesignGroupId but same LegacyKey RN of second row should be 3 and so on. At the end I want to get ALL last DesignKey inside each DesignGroupId inside each LegacyKey

What am I doing wrong?

Another example

+-----------+-----------+-----------+--------------------------------------+----+
| LegacyKey | DesignKey | StatusKEy |            DesignGroupId             | RN |
+-----------+-----------+-----------+--------------------------------------+----+
|     18288 |      3974 |        63 | F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |  1 |
|     18288 |      4096 |       107 | F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |  2 |
|     18288 |      7224 |        66 | F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |  3 |
|     18288 |      4842 |        66 | A18320D8-C7A8-4A2A-AFDE-38A483C24E81 |  1 |
|     18289 |      7325 |        66 | 90C224D9-2514-4294-8DEC-D3EE16EC2D00 |  1 |
|     18289 |      3975 |        63 | 90C224D9-2514-4294-8DEC-D3EE16EC2D00 |  2 |
+-----------+-----------+-----------+--------------------------------------+----+

In this case I have two different LegacyKey but result is wrong because it returns all RN of same DesignGroupId and I only want last one. Desire result is:

+-----------+-----------+-----------+--------------------------------------+----+
| LegacyKey | DesignKey | StatusKEy |            DesignGroupId             | RN |
+-----------+-----------+-----------+--------------------------------------+----+
|     18288 |      7224 |        66 | F18320D8-C7A8-4A2A-AFDE-38A483C24E81 |  3 |
|     18288 |      4842 |        66 | A18320D8-C7A8-4A2A-AFDE-38A483C24E81 |  1 |
|     18289 |      3975 |        63 | 90C224D9-2514-4294-8DEC-D3EE16EC2D00 |  2 |
+-----------+-----------+-----------+--------------------------------------+----+

As you can see I get last RN of DesignGroupId, but they are in the same LegacyKey

解决方案

I would not expect a GROUP BY in the subquery. And I would expect filtering in the outer query:

WITH CTE2 (LegacyKey, DesignKey, StatusKey, DesignGroupId, RN) AS (
      SELECT LegacyKey, DesignKey, StatusKey, DesignGroupId,
             ROW_NUMBER() OVER (PARTITION BY [LegacyKey], [DesignGroupId] ORDER BY [DesignGroupId] DESC) AS seqnum
      FROM @DeadDesigns 
     )
SELECT *
FROM CTE2
WHERE seqnum = 1;

Note that I changed the ORDER BY to DESC -- that is typical when you want the "last" or "most recent" of something.

这篇关于使用CTE获取每个组的最后一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！