Início / dba / Perguntas / 189545
Accepted
Gary
Asked: 2017-10-28 17:03:39 +0800 CST

Sempre retorne X linhas com linha inicial variável

  • 772

Meu objetivo é retornar X linhas de uma tabela, onde o valor de X é algum valor nos 100 baixos. Para fins de teste dessa consulta, estou experimentando diferentes valores X.

Esta é uma visão da paginação para preencher uma grade em uma interface de usuário. Tentei usar Offset / Fetch nesta consulta, mas não consegui fazer com que funcionasse com minha variável @Partial.

@Partial é uma string que identificará o número de linha inicial e todos os números de linha que seguem a primeira correspondência. Isso é bem simples até que @Partial retorne a última linha. Nesse caso, quero retornar a última linha mais as linhas X - 1 anteriores.

Minha consulta parece estar funcionando, mas sinto que estou pensando demais no problema. Eu fiz horas de testes e pesquisas online. Eu forneci uma consulta de trabalho completa que será executada.

Quando @Partial é nulo, a consulta obedece às variáveis ​​@StartRow e @NumWindowRows. Caso contrário, @NumWindowRows será retornado, começando ou terminando com a string correspondente.

Obrigado por qualquer ajuda nesta consulta. Para recapitular, estou obtendo os resultados pretendidos, mas estou procurando orientação sobre uma abordagem ou método mais simples para melhorar a eficiência.

/***
Dynamic variables
******/
DECLARE 
@StartRowNum INT = 1
,@NumWindowRows INT = 3
,@Partial NVARCHAR(10) = 'M';

/*****
Test table
*******/
DECLARE @Customers TABLE (
    CustomerID VARCHAR(10)
    ,[Name] VARCHAR(155)
    );
/***
Generate test data
*****/
INSERT INTO @Customers(CustomerID, [Name])
SELECT Char(number+65), 'Customer ' +Char(number+65) 
  FROM master.dbo.spt_values
 WHERE name IS NULL AND 
       number < 26

/***
Return @NumWindowRows
******/

;WITH FullResult AS (
        SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
        ,COUNT(*) OVER () AS FullCount
        ,CustomerID
        ,[Name]
        FROM @Customers),
PartialResult AS (SELECT *, COUNT(*) OVER () AS FullCount
        FROM 
        (SELECT ROW_NUMBER() OVER (ORDER BY CustomerID) AS RowNum
            ,CustomerID
            ,[Name]
         FROM @Customers) c
         WHERE CustomerID >= @Partial)

SELECT f.*
FROM FullResult f
LEFT OUTER JOIN PartialResult p ON f.RowNum = p.RowNum
WHERE (@Partial IS NULL AND f.RowNum >= @StartRowNum
        AND f.RowNum <= @NumWindowRows + @StartRowNum -1)
OR (f.RowNum >= CASE 
                WHEN (SELECT MAX(FullCount) FROM PartialResult) < @NumWindowRows
                THEN ((SELECT MIN(RowNum) FROM PartialResult) - (@NumWindowRows - (SELECT MAX(FullCount) FROM PartialResult)))
                ELSE (SELECT MIN(RowNum) FROM PartialResult)    END
        AND f.RowNum <= CASE 
                WHEN (SELECT MAX(FullCount) FROM PartialResult) > @NumWindowRows
                THEN ((SELECT MAX(RowNum) FROM PartialResult) - ((SELECT MAX(FullCount) FROM PartialResult) - @NumWindowRows))
                WHEN (SELECT MAX(FullCount) FROM PartialResult) + @StartRowNum < @NumWindowRows
                THEN f.FullCount
                ELSE (SELECT MAX(RowNum) FROM PartialResult) END
            )
sql-server t-sql

1 respostas

  1. Best Answer

    Ok, então precisamos descobrir quantas linhas estão na tabela e quantas linhas restam depois de movermos para o marcador definido por @Partial.

    -- simple variable to hold the offset:
DECLARE @offset int;

;WITH x(o,c) AS 
(
  SELECT 
    -- how many rows are at or past the @Partial marker?
    SUM(CASE WHEN CustomerID >= @Partial THEN 1 END),
    -- how many rows total?
    COUNT(*) 
  FROM @Customers
 ) 
 -- the magic: a conditional that determines if we're 
 -- too close to the end, and subtracts the difference
 -- between how close we are and how close we want to be
 SELECT @offset = (c-o) - CASE 
   WHEN @NumWindowRows > (c - (c-o)) THEN
       (@NumWindowRows - (c - (c-o))) 
   ELSE 0 END
FROM x;

SELECT 
  ROW_NUMBER() OVER (ORDER BY CustomerID),
  CustomerID, 
  Name
FROM @Customers
ORDER BY CustomerID
OFFSET @offset ROWS
FETCH NEXT @NumWindowRows ROWS ONLY;

    Você provavelmente poderia fazer tudo isso em linha em vez de usar a @offsetvariável, mas fica confuso muito rápido, e não acho que seja possível evitar a verificação adicional necessária para derivar essas contagens de qualquer maneira.

    Isso é muito mais simples do que sua lógica. Mas pressupõe que você sempre fará o pedido CustomerIDe nunca adicionará filtros adicionais. Se houver menos de @NumWindowRowslinhas na tabela, bem...

    • 1

relate perguntas