gpt4 book ai didi

tsql - 如何在以下过程中使用 ROW_NUMBER?

转载 作者:行者123 更新时间:2023-12-04 23:11:11 27 4
gpt4 key购买 nike

我有以下存储过程返回 A , B , 并按降序计数。我正在尝试使用 ROW_NUMBER ,所以我可以分页记录,但我想要第一行号 1成为计数最高的记录,所以基本上,如果我返回一个包含 3 条记录的表并且计数为 30 , 20 , 10 ,然后是行号 1应该对应计数 30 , 行号 2应该对应计数 20 , 和行号 3应该对应计数 10 . dbo.f_GetCount是一个返回计数的函数。

create procedure dbo.Test
as
@A nvarchar(300) = NULL,
@B nvarchar(10) = NULL
as

select @A = nullif(@A,'')
,@B = nullif(@B,'');

select h.A
,hrl.B
,dbo.f_GetCount(hrl.A,h.B) as cnt
from dbo.hrl
inner join dbo.h
on h.C = hrl.C
where(@A is null
or h.A like '%'+@A+'%'
)
and (@B is null
or hrl.B = @B
)
group by hrl.B
,h.A
order by cnt desc;

最佳答案

WITH q AS
(
SELECT h.A, hrl.B,
dbo.f_GetCount(hrl.A,h.B) as cnt
FROM dbo.hrl
INNER JOIN dbo.h on h.C = hrl.C
WHERE (@A IS NULL OR h.A like '%' + @A + '%')
AND (@B IS NULL OR hrl.B = @B)
GROUP BY hrl.B, h.A
)
SELECT q.*, ROW_NUMBER() OVER (ORDER BY cnt DESC) AS rn
FROM q
ORDER BY rn DESC

先取回 10行,使用:
WITH q AS
(
SELECT h.A, hrl.B,
dbo.f_GetCount(hrl.A,h.B) as cnt
FROM dbo.hrl
INNER JOIN dbo.h on h.C = hrl.C
WHERE (@A IS NULL OR h.A like '%' + @A + '%')
AND (@B IS NULL OR hrl.B = @B)
GROUP BY hrl.B, h.A
)
SELECT TOP 10 q.*,
ROW_NUMBER() OVER (ORDER BY cnt DESC, A, B) AS rn
FROM q
ORDER BY cnt DESC, A, B

检索 11 之间的行和 20 , 用:
SELECT  *
FROM (
WITH q AS
(
SELECT h.A, hrl.B,
dbo.f_GetCount(hrl.A,h.B) as cnt
FROM dbo.hrl
INNER JOIN dbo.h on h.C = hrl.C
WHERE (@A IS NULL OR h.A like '%' + @A + '%')
AND (@B IS NULL OR hrl.B = @B)
GROUP BY hrl.B, h.A
)
SELECT q.*,
ROW_NUMBER() OVER (ORDER BY cnt DESC, A, B) AS rn
FROM q
) qq
WHERE rn BETWEEN 11 AND 20
ORDER BY cnt DESC, A, B

关于tsql - 如何在以下过程中使用 ROW_NUMBER?,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/706720/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com