如何在 SEDE 上对徽章表进行分页? [英] How to paginate the badge table on SEDE?

问题描述

从这个示例中获取代码:

-- UserId: User ID     "Enter a user's ID."
SELECT      u.Id     AS [User Link]
            , CASE
                WHEN b.Tagbased = 1 THEN 'Tag: [' + b.Name + ']'
                ELSE b.Name
            END    AS [Badge name]
            , CASE
                WHEN b.Class = 1 THEN 'Gold'
                WHEN b.Class = 2 THEN 'Silver'
                WHEN b.Class = 3 THEN 'Bronze'
                ELSE '*Unkown*'
            END    AS [Class]
            , b.Date AS [Awarded]
FROM        Badges b
LEFT JOIN   Users u  ON u.Id = b.Userid
--WHERE       b.Userid = ##UserId:int##
WHERE       u.reputation > 100000
ORDER  BY   u.Displayname
            , [Awarded] DESC

如何实现分页像这样的例子来接收超过 50000 的结果?

How is it possible to implement a pagination like this example to receive the results which are more than 50000?

推荐答案

使用 CTE 方法(据称比 OFFSET 和 FETCH 更快)，查询变为:
(参见现场直播)

Using the CTE method (which is allegedly faster than OFFSET and FETCH), the query becomes:
(See it live at SEDE)

-- StartRow: Starting row for paging
-- EndRow: Ending row for paging (Max 50K rows at a time)
-- MinimumRep: Ony return data for users that have this many reputation points
WITH allData AS (
    SELECT
                ROW_NUMBER() OVER (ORDER BY u.Displayname, b.Date DESC) AS row
                , u.Id
                , CASE
                    WHEN b.Tagbased = 1 THEN 'Tag: [' + b.Name + ']'
                    ELSE b.Name
                END    AS [Badge name]
                , CASE
                    WHEN b.Class = 1 THEN 'Gold'
                    WHEN b.Class = 2 THEN 'Silver'
                    WHEN b.Class = 3 THEN 'Bronze'
                    ELSE '*Unkown*'
                END    AS [Class]
                , b.Date AS [Awarded]
    FROM        Users u
    INNER JOIN  Badges b    ON u.Id = b.Userid
    WHERE       u.reputation > ##MinimumRep:int?100000##
)
SELECT      a.Id     AS [User Link]
            , a.[Badge name]
            , a.[Class]
            , a.[Awarded]
FROM        allData a
WHERE       row    >= ##StartRow:INT?1##
AND         row    <= ##EndRow:INT?50000##
ORDER BY    row

这篇关于如何在 SEDE 上对徽章表进行分页?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！