首页
学习
活动
专区
工具
TVP
发布
社区首页 >问答首页 >获取过滤不影响行号的绝对T-SQL行号

获取过滤不影响行号的绝对T-SQL行号
EN

Stack Overflow用户
提问于 2018-06-22 22:07:20
回答 1查看 87关注 0票数 -2

目前,我有一个sql脚本,它抓取了几十万条记录,我想要对数据进行分页。

分页在很大程度上是有效的,但我的问题是,当从获得行号的子查询中过滤结果时,行号实际上总是从1开始,而我希望它做的是返回在子查询期间分配的行号(如果这是有意义的话)。

我所拥有的是:

SELECT * FROM (
        SELECT DISTINCT --TOP(@End-@Start)
        EF.FulfillmentID
        ,COALESCE(GEN.GroupID, GEL.GroupID) AS GroupID
        ,COALESCE(GEN.GroupName, GEL.GroupName) AS GroupName
        ,EF.PlanEnrollmentID
        ,EF.PlanElectionID
        ,COALESCE(EE2.EmployeeID, EE.EmployeeID) AS EmployeeID
        ,COALESCE(EE2.FirstName, EE.FirstName) AS FirstName
        ,COALESCE(EE2.LastName, EE.LastName) AS LastName
        ,COALESCE(EPE2.BeginDate, EPE.EffectiveDate) AS EffectiveDate
        ,COALESCE(EPE2.EndDate, EPE.TerminationDate) AS TerminationDate
        ,COALESCE(CPEN.CarrierProductID, CPEL.CarrierProductID) AS CarrierProductID
        ,COALESCE(CPEN.CarrierProductName, CPEL.CarrierProductName) AS CarrierProductName
        ,EF.FulFillmentTypeID
        ,FT.FulfillmentTypeDesc
        ,EF.FulfillmentComplete
        ,CAST(EF.RunFulfillment AS BIT) AS RunFulfillment
        ,CAST(@CanModify AS BIT) AS CanModify
        ,ROW_NUMBER() over (order by EF.FulfillmentID) as Num
    FROM
        dbo.E_Fulfillment AS EF
        JOIN appconfig.FulfillmentTypes AS FT ON FT.FulfillmentTypeID=EF.FulFillmentTypeID
        JOIN(
            SELECT
                MAX(FulfillmentID) AS MaxFulfillmentID
            FROM
                dbo.E_Fulfillment
            GROUP BY
                COALESCE(PlanElectionID, PlanEnrollmentID)
            ) AS MX ON MX.MaxFulfillmentID=EF.FulfillmentID

        LEFT JOIN dbo.E_PlanEnrollment AS EPE ON EPE.PlanEnrollmentID=EF.PlanEnrollmentID
        LEFT JOIN dbo.GroupProductPlanOptions AS GPPOEN ON GPPOEN.GroupProductPlanOptionID=EPE.GroupProductPlanOptionID
        LEFT JOIN dbo.GroupProducts AS GPEN ON GPEN.GroupProductID=GPPOEN.GroupProductID
        LEFT JOIN ref.CarrierProducts AS CPEN ON CPEN.CarrierProductID=GPEN.CarrierProductID
        LEFT JOIN dbo.Groups AS GEN ON GEN.GroupID=GPEN.GroupID
        LEFT JOIN dbo.E_Employees EE ON EE.EmployeeID=EPE.EmployeeID

        LEFT JOIN dbo.E_PlanElections AS EPE2 ON EPE2.PlanElectionID=EF.PlanElectionID
        LEFT JOIN dbo.GroupProductPlanOptions AS GPPOEL ON GPPOEL.GroupProductPlanOptionID=EPE2.GroupProductPlanOptionID
        LEFT JOIN dbo.GroupProducts AS GPEL ON GPEL.GroupProductID=GPPOEL.GroupProductID
        LEFT JOIN ref.CarrierProducts AS CPEL ON CPEL.CarrierProductID=GPEL.CarrierProductID
        LEFT JOIN dbo.Groups AS GEL ON GEL.GroupID=GPEL.GroupID
        LEFT JOIN dbo.E_Employees AS EE2 ON EE2.EmployeeID=EPE2.EmployeeID

    WHERE
        ((GEN.GroupID=@GroupID OR GEN.GroupID IS NULL)
        OR (EPE2.EmployeeID=@EmployeeID OR EPE2.EmployeeID IS NULL)
        OR (@GroupID IS NULL AND @EmployeeID IS NULL))
        AND FulfillmentID > @KeyIndex
        ) as MyDerivedTable

        where (MyDerivedTable.Num BETWEEN @Start AND @End)
        AND FulfillmentID > @KeyIndex
        order by FulfillmentID

所以为了描述一下发生了什么,@Start、@End和@KeyIndex都是输入参数。

@Start/@End =在行处开始/结束(它工作得很好;如果@KeyIndex为0,则在我需要的位置开始和结束)

@KeyIndex =开始PrimaryKey > @KeyIndex (这就是行号成为问题的地方)。

我认为可能发生的情况是@KeyIndex之前的记录没有被计算,但我不明白这是为什么,因为子查询保存行号,并且我假设它在父查询完成之前被处理?

where (MyDerivedTable.Num BETWEEN @Start AND @End)
AND FulfillmentID > @KeyIndex
order by FulfillmentID

有什么建议吗?

EN

回答 1

Stack Overflow用户

发布于 2018-06-22 23:27:14

您知道AND FulfillmentID > @KeyIndex也在您的子查询中吗?

不会计算@KeyIndex之前的记录,因为您告诉它不要处理这些记录。

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/50989786

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档