问题描述
我有这样的查询:
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获取每个组的最后一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!