SQL Server нумерация строк и отфильтрованные результаты

У меня есть приложение, в котором мне нужно отобразить "страницы" данных в списке. Основная идея довольно распространена. На дисплее отображается список элементов, а в нижней части дисплея находятся какие-то элементы управления, которые позволяют перейти к следующей "странице" данных.

Все хорошо и хорошо. У меня это работает.

Ниже приведен SQL в представлении, которое я использую для поддержки" следующего " поведения.

CREATE VIEW CampaignParticipants AS
SELECT  row_number() OVER (ORDER BY TPT.LastName, TPT.FirstName, TPT.DoB) AS RowNumber
        ,CGP.*
        ,TPT.*
FROM    tblCampaignGEDPush CGP
JOIN    tblParticipants TPT
ON      CGP.PartID = TPT.PartID

Вот как я использую вид 

SELECT  *
FROM    CampaignParticipants
WHERE   RowNumber >= 0
AND     RowNumber <= 100

Это имитирует захват "первого страница " из 100 результатов просмотра. Страницы через каждый набор результатов просто персиковые.

Отлично.. Но:

Как некоторые из вас, кто имел дело с этим, вероятно, знают, это неправильно. Если я хочу искать по TPT.LastName like 'R%' и получить первый набор результатов, я обречен. Я собираюсь начать смотреть на RowNumber = 0, остановиться на RowNumber = 100, но результаты "R", вероятно, будут далеко за пределами этого диапазона. Итог: список возвращается пустым.

И он становится более липким: пользователь хочет иметь возможность " фильтровать" на фамилию, имя, доб, местоположение, телефон, почтовый индекс, что угодно.

**edit: я действительно не могу поместить фильтр на" внутренний " запрос, так как он находится в представлении, и фильтр может меняться произвольно

У кого-нибудь есть идеи, как получить этот результирующий набор, имеющий row_number() на отфильтрованный набор результатов?

3 3
sql-server row-number

3 ответа:

Что-то вроде этого должно быть сделано...

SELECT * FROM (
SELECT  *, ROW_NUMBER() OVER (ORDER BY LastName, FirstName, DoB) AS __RN
FROM    CampaignParticipants
WHERE   LastName LIKE 'R%') innerData WHERE __RN BETWEEN 1 and 100

Однако вы должны использовать имена столбцов, а не '*'. Я не знаю, как выглядят ваши столы, поэтому не могу заполнить это для вас.

4

Прежде всего попробуйте использовать proc вместо view, и поскольку пользователи просят вас иметь так много фильтрации, то proc-это единственное решение, которое вы получили.Inside proc фильтрует данные, используя все эти фильтры, а затем генерирует row_number и затем показывает, скажем, первые 100 записей или что-то в этом роде.

1

Я бы хотел, чтобы было что-то более элегантное, чем это. Это мое сохраненное решение proc. Лучшее, что могут придумать мои хромые навыки SQL.

Параметр OUT в списке параметров предназначен для общего числа строк в этом наборе, поэтому интерфейс знает, сколько страниц вместе с этим конкретным набором фильтров.

CREATE PROC [dbo].[procCampaignGEDPushSelect]
    @LastName       VARCHAR(50)     = null
    ,@FirstName     VARCHAR(50)     = null
    ,@Location      VARCHAR(255)    = null
    ,@DoB           DateTime        = null
    ,@Zip           VARCHAR(50)     = null
    ,@Phone         VARCHAR(50)     = null
    ,@Email         VARCHAR(255)    = null
    ,@Gender        VARCHAR(20)     = null
    ,@IsGED         Bit             = 0
    ,@IsBTT         Bit             = 0
    ,@IsOACE        Bit             = 0
    ,@Completed     Bit             = 0
    ,@TotalCount    INT             OUT
AS
BEGIN

SELECT @LastName    = @LastName     + '%'
SELECT @FirstName   = @FirstName    + '%' 
SELECT @Location    = @Location     + '%' 
SELECT @Zip         = @Zip          + '%' 
SELECT @Phone       = @Phone        + '%' 
SELECT @Email       = @Email        + '%' 
SELECT @Gender      = @Gender       + '%' 

SELECT     row_number() OVER (ORDER BY LastName, FirstName, DoB) AS RowNumber
    , TPT.LastName
    , TPT.FirstName
    , TPT.WF1Site
    , TPT.DOB
    , TPT.Zip
    , TPT.Telephone
    , TPT.CellPhone
    , TPT.Email
    , TPT.Gender
    , TPT.IsBTT
    , TPT.IsGED
    , TPT.IsOACE
    , TPT.IsSRS
    ,CGP.*

FROM        tblCampaignGEDPush CGP

JOIN        tblParticipants TPT
ON          CGP.PartID = TPT.PartID

WHERE       1=1

AND         1 = (CASE WHEN @LastName    IS NOT NULL THEN (CASE WHEN TPT.LastName    LIKE @LastName  THEN 1 ELSE 0 END) ELSE 1 END)  
AND         1 = (CASE WHEN @FirstName   IS NOT NULL THEN (CASE WHEN TPT.FirstName   LIKE @Firstname THEN 1 ELSE 0 END) ELSE 1 END)
AND         1 = (CASE WHEN @Location    IS NOT NULL THEN (CASE WHEN TPT.WF1Site     LIKE @Location  THEN 1 ELSE 0 END) ELSE 1 END)  
AND         1 = (CASE WHEN @Zip         IS NOT NULL THEN (CASE WHEN TPT.Zip         LIKE @Zip       THEN 1 ELSE 0 END) ELSE 1 END) 
AND
(           1 = (CASE WHEN @Phone       IS NOT NULL THEN (CASE WHEN TPT.Telephone   LIKE @Phone     THEN 1 ELSE 0 END) ELSE 1 END) 
    OR      1 = (CASE WHEN @Phone       IS NOT NULL THEN (CASE WHEN TPT.CellPhone   LIKE @Phone     THEN 1 ELSE 0 END) ELSE 1 END) 
)
AND         1 = (CASE WHEN @Email       IS NOT NULL THEN (CASE WHEN TPT.Email       LIKE @Email     THEN 1 ELSE 0 END) ELSE 1 END) 
AND         1 = (CASE WHEN @Gender      IS NOT NULL THEN (CASE WHEN TPT.Gender      LIKE @Gender    THEN 1 ELSE 0 END) ELSE 1 END) 
AND         1 = (CASE WHEN @DoB         IS NOT NULL THEN (CASE WHEN TPT.DoB         = @DoB          THEN 1 ELSE 0 END) ELSE 1 END) 
AND         1 = (CASE WHEN @IsGED       != 0        THEN (CASE WHEN TPT.IsGED       = 1             THEN 1 ELSE 0 END) ELSE 1 END) 
AND         1 = (CASE WHEN @IsBTT       != 0        THEN (CASE WHEN TPT.IsBTT       = 1             THEN 1 ELSE 0 END) ELSE 1 END) 
AND         1 = (CASE WHEN @IsOACE      != 0        THEN (CASE WHEN TPT.IsOACE      = 1             THEN 1 ELSE 0 END) ELSE 1 END) 

AND         1 = (CASE WHEN @Completed   != 0        THEN (CASE WHEN CGP.Completed   = 1             THEN 1 ELSE 0 END) ELSE 1 END)

ORDER BY    TPT.LastName
            , TPT.FirstName
            , TPT.DoB


SELECT @TotalCount = @@ROWCOUNT

END

И тогда я начал думать. Вместо того, чтобы использовать этот хитрый, подверженный ошибкам proc (который, кстати, работает довольно хорошо), так как я нахожусь в .NET, я задаюсь вопросом, есть ли хороший жесткий решение есть.

Теперь я знаю, что исходный вопрос не имеет ничего общего с .NET, поэтому я оставляю вышеприведенный proc доступным для тех, кто заинтересован в строгом решении SQL, которое, я думаю, работает довольно хорошо.

Поэтому я начал копаться в интерфейсе IQueryable и нашел золото: 

IQueryable<queryParticipant>    qparticipant = db.queryParticipants.AsQueryable();
...
qparticipant = qparticipant.Where( ... any filter you choose );
...

return qparticipant

    .OrderBy( p => p.LastName )
    .OrderBy( p => p.FirstName )
    .OrderBy( p => p.DOB )
    .Select( ... whatever you like ... )
    .Skip( StartRecordNumber )          // This is the trick! Start the query here..
    .Take( PageSize )                   // Take only as many as you need
    ;

И это все. Интернет .Чистый подход-это хорошо, если доступно. Сохраненный Proc отлично подходит, когда такой API недоступен.

1