尝试按顺序使用 DENSE_RANK() 添加新列

发布于 2025-01-12 09:09:37 字数 3454 浏览 0 评论 0原文

我希望一些例子能帮助解释这种情况。

SELECT 
    ID,
    --ROW_NUMBER() OVER (PARTITION BY CardNumber ORDER BY ID DESC) AS 'RN',
    DENSE_RANK() OVER (ORDER BY CardNumber DESC) AS Rank,
    CardNumber,
    StampNumber,
    AuditDate,
FROM [dbo].[XXXX]
ORDER BY ID DESC, AuditDate DESC, StampNumber DESC

我已经阅读了 DENSE_RANK() ，它最接近我正在寻找的内容，但并不完全在那里。运行这段代码给我

ID	Rank	CardNumber	StampNumber	AuditDate
46	1	3	20	2022-03-07 03:45:50.343
45	1	3	20	2022-03-07 03:45:50.343
44	2	2	40	2022-03-07 03:45:50.343
43	2	2	30	2022-03-07 03:45:50.343
42	2	2	20	2022-03-07 03:45:50.343
41	2	2	10	2022-03-07 03:45:50.343
40	3	1	40	2022-03-07 03:45:50.343
39	3	1	30	2022-03-07 03:45:50.343
38	3	1	20	2022-03-07 03:45:50.343
37	3	1	10	2022-03-07 03:45:50.343
36	1	3	40	2022-03-07 03:45:50.343
35	1	3	30	2022-03-07 03:45:50.343
34	1	3	20	2022-03-07 03:45:50.343
33	1	3	10	2022-03-07 03:45:50.343

我正在寻找的结果是

ID	Rank	CardNumber	StampNumber	AuditDate
46	1	3	20	2022-03-07 03:45:50.343
45	1	3	20	2022-03-07 03:45:50.343
44	2	2	40	2022-03-07 03:45:50.343
43	2	2	30	2022-03-07 03:45:50.343
42	2	2	20	2022-03-07 03:45:50.343
41	2	2	10	2022-03-07 03:45:50.343
40	3	1	40	2022-03-07 03:45:50.343
39	3	1	30	2022-03-07 03:45:50.343
38	3	1	20	2022-03-07 03:45:50.343
37	3	1	10	2022-03-07 03:45:50.343
36	4	3	40	2022-03-07 03:45:50.343
35	4	3	30	2022-03-07 03:45:50.343
34	4	3	20	2022-03-07 03:45:50.343
33	4	3	10	2022-03-07 03:45:50.343

我希望密集排名仍能对排名进行分组按 CardNumber 但需要排名列按顺序增长重置。我只想获得前三名。

原文

I'm hoping some examples will help explain the situation.

SELECT 
    ID,
    --ROW_NUMBER() OVER (PARTITION BY CardNumber ORDER BY ID DESC) AS 'RN',
    DENSE_RANK() OVER (ORDER BY CardNumber DESC) AS Rank,
    CardNumber,
    StampNumber,
    AuditDate,
FROM [dbo].[XXXX]
ORDER BY ID DESC, AuditDate DESC, StampNumber DESC

I've read up on DENSE_RANK() and it's the closest to what I'm looking for but not quite there.
Running this block of code gives me

ID	Rank	CardNumber	StampNumber	AuditDate
46	1	3	20	2022-03-07 03:45:50.343
45	1	3	20	2022-03-07 03:45:50.343
44	2	2	40	2022-03-07 03:45:50.343
43	2	2	30	2022-03-07 03:45:50.343
42	2	2	20	2022-03-07 03:45:50.343
41	2	2	10	2022-03-07 03:45:50.343
40	3	1	40	2022-03-07 03:45:50.343
39	3	1	30	2022-03-07 03:45:50.343
38	3	1	20	2022-03-07 03:45:50.343
37	3	1	10	2022-03-07 03:45:50.343
36	1	3	40	2022-03-07 03:45:50.343
35	1	3	30	2022-03-07 03:45:50.343
34	1	3	20	2022-03-07 03:45:50.343
33	1	3	10	2022-03-07 03:45:50.343

The result I'm looking for is

ID	Rank	CardNumber	StampNumber	AuditDate
46	1	3	20	2022-03-07 03:45:50.343
45	1	3	20	2022-03-07 03:45:50.343
44	2	2	40	2022-03-07 03:45:50.343
43	2	2	30	2022-03-07 03:45:50.343
42	2	2	20	2022-03-07 03:45:50.343
41	2	2	10	2022-03-07 03:45:50.343
40	3	1	40	2022-03-07 03:45:50.343
39	3	1	30	2022-03-07 03:45:50.343
38	3	1	20	2022-03-07 03:45:50.343
37	3	1	10	2022-03-07 03:45:50.343
36	4	3	40	2022-03-07 03:45:50.343
35	4	3	30	2022-03-07 03:45:50.343
34	4	3	20	2022-03-07 03:45:50.343
33	4	3	10	2022-03-07 03:45:50.343

I'd like the dense rank to still group the rank by the CardNumber but need the rank column to grow sequentially instead of resetting.
I'm looking to only grab the top 3 ranks.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

┈┾☆殇 2025-01-19 09:09:37

这是一种间隙与岛屿问题。您正在尝试获取每组相同 CardNumber 值的排名数字（其中无间隙），按 ID DESC 排序时。

为此，您不能使用 DENSE_RANK 或 ROW_NUMBER，因为它们会将具有相同 CardNumber 值的所有行放在一起。

有多种解决方案。这是一个：

WITH PrevValues AS (
     SELECT *,
       IsNewCardNumber = CASE WHEN CardNumber = LAG(CardNumber) OVER (ORDER BY ID DESC)
                        THEN NULL ELSE 1 END
     FROM XXXX
)
SELECT
  ID,
  Rank = COUNT(IsNewCardNumber) OVER (ORDER BY ID DESC),
  CardNumber,
  StampNumber,
  AuditDate
FROM PrevValues;

db<>fiddle

This is a type of gaps-and-islands problem. You are trying to get a ranking number for each group of identical CardNumber values (with no gaps), when ordered by ID DESC.

You cannot use DENSE_RANK or ROW_NUMBER for this, because they will place all rows with the same CardNumber value together.

There are a number of solutions. Here is one:

WITH PrevValues AS (
     SELECT *,
       IsNewCardNumber = CASE WHEN CardNumber = LAG(CardNumber) OVER (ORDER BY ID DESC)
                        THEN NULL ELSE 1 END
     FROM XXXX
)
SELECT
  ID,
  Rank = COUNT(IsNewCardNumber) OVER (ORDER BY ID DESC),
  CardNumber,
  StampNumber,
  AuditDate
FROM PrevValues;

db<>fiddle

回复收藏 0 原文

~没有更多了~