Início / dba / Perguntas / 124896
Accepted
Der Kommissar
Asked: 2015-12-31 14:08:48 +0800 CST

Atualizando uma cláusula WHERE para verificar se um valor NÃO está em uma tabela separada

  • 772

Eu tenho uma consulta que usa uma WHEREcláusula e uso exatamente a mesma WHEREcláusula em muitas consultas nesta tabela (et al).

A consulta é:

SELECT
    DATENAME(DW, [AtDateTime]) AS [Day of Week]
    ,COUNT(*) AS [Number of Searches]
    ,CAST(CAST(COUNT(*) AS DECIMAL(10, 2)) 
         / COUNT(DISTINCT CONVERT(DATE, [AtDateTime])) AS DECIMAL(10, 2)) 
       AS [Average Searches per Day]
    ,SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END) 
       AS [Number of Searches with no Results]
    ,CAST(CAST(SUM(CASE WHEN [NumFound] = 0 THEN 1 ELSE 0 END) 
         AS DECIMAL(10, 2)) / COUNT(*) AS DECIMAL(10, 4)) 
       AS [Percent of Searches with no Results]
FROM [DB].[dbo].[SearchHistory] 
WHERE 
    [CustomerNumber] <> '1234' AND [CustomerNumber] <> '5678'
GROUP BY DATENAME(DW, [AtDateTime]), DATEPART(DW, [AtDateTime])
ORDER BY DATEPART(DW, [AtDateTime])

A parte que desejo alterar é a WHEREcláusula, para permitir que eu use uma tabela para que, se eu tiver que adicionar um número de cliente a ser ignorado, não precise atualizar todas as minhas consultas. (E há algumas consultas que têm essa mesma WHEREcláusula.)

sql-server sql-server-2012

3 respostas

  1. CREATE TABLE dbo.CustomerExclusions
(
  CustomerNumber VARCHAR(32) PRIMARY KEY -- Is CustomerNumber *really* a string?
);

INSERT dbo.CustomerExclusions(CustomerNumber) VALUES('1234'),('5678');

    Agora sua WHEREcláusula em todas as consultas se torna:

    WHERE NOT EXISTS 
(
  SELECT 1 FROM dbo.CustomerExclusions AS c
  WHERE c.CustomerNumber = SearchHistory.CustomerNumber
)
    • 7
  2. Best Answer

    Crie uma tabela para conter os números de clientes a serem excluídos e, em seguida, exclua essas linhas usando a NOT EXISTSna WHEREcláusula.

    CREATE TABLE dbo.ExcludedCustomers
(
    CustomerNumber VARCHAR(255) NOT NULL
        CONSTRAINT PK_ExcludedCustomers
        PRIMARY KEY CLUSTERED
);

INSERT INTO dbo.ExcludedCustomers (CustomerNumber)
VALUES ('1234')
    , ('5678');


SELECT
    <....>
FROM [DB].[dbo].[SearchHistory] 
WHERE 
    NOT EXISTS (
        SELECT 1
        FROM dbo.ExcludedCustomers ec
        WHERE ec.CustomerNumber = SearchHistory.CustomerNumber
    )
    <...>;
    • 5

  3. Existem questões importantes/problemas potenciais com a abordagem proposta. Com certeza, você pode excluir com bastante facilidade por meio de uma tabela de trabalho 'exclusão de número de cliente':

    WHERE NOT EXISTS (
  SELECT 1 FROM [dbo].Work_ExcludeCustomer
  WHERE CustomerNumber = SearchHistory.CustomerNumber
)

    Mas agora, o que eram "parâmetros de consulta" -- totalmente dinâmicos e independentes, por consulta e por usuário -- estão se transformando em "estado persistente compartilhado no banco de dados".

    Algumas perguntas e pontos relevantes:

    1. as informações de exclusão do cliente devem ser separadas, por usuário ou por sessão? você pode adicionar um parâmetro 'SessionID' para distingui-los, mas essencialmente você está recriando um antigo padrão de "mesa de trabalho".

    2. talvez uma cláusula NOT IN(...) seja preferível? que podem ser parametrizados dinamicamente, até o limite de 2100 parâmetros.

    3. talvez revisite seu código/infraestrutura para criar consultas e vincular parâmetros, se você atualmente depende de números de parâmetros fixos; melhorar isso permitirá a modularidade e o uso de cláusulas IN ou NOT IN (?, ?, ?..) com um número variável de parâmetros.

    Abordagem sugerida:

    WHERE [CustomerNumber] NOT IN (?, ?, ?)

    Com ligações '1234', '5678', '6789' etc para os parâmetros NOT IN () e parâmetros de consulta lógica subsequentes vinculados à numeração apropriada dinamicamente.

    • -3

relate perguntas