Início / dba / Perguntas / 131350
Accepted
MSIS
Asked: 2016-03-05 22:45:46 +0800 CST

Subconsulta correlacionada no SQL Server 2014

  • 772

Estou executando uma subconsulta correlacionada para saber a listagem de fornecedores (pelo Vendor Name) que estão em diferentes cidades, estados, ou seja, queremos saber os fornecedores que não possuem cidade e estado em comum com outros fornecedores. Parecia que uma autojunção era a coisa certa a fazer.

Apenas dicas, se possível, por favor.

A tabela de fornecedores é:

Vendors(VendorID P, VendorCity, VendorState, VendorName,...)

Isto é o que eu tenho:

Select VendorName, VendorCity, VendorState from Vendors AS V1 where
         VendorCity, VendorState NOT IN (Select VendorCity, VendorState FROM
          Vendors AS V2 where V2.VendorID <> V1.VendorID)

Esta é a mensagem de erro que recebo:

Msg 4145, Nível 15, Estado 1, Linha 2 Uma expressão de tipo não booleano especificada em um contexto onde uma condição é esperada, próximo a ','.

Não vejo por que há uma referência a tipos booleanos, já que não é um EXISTS ou outra consulta relacionada.

sql-server subquery

3 respostas

  1. Best Answer

    A sintaxe para IN(...) é:

    test_expression [ NOT ] IN 
    ( subquery | expression [ ,...n ]    )

    Com:

    expressão_teste

    É qualquer expressão válida.

    subconsulta

    É uma subconsulta que possui um conjunto de resultados de uma coluna . Esta coluna deve ter o mesmo tipo de dados que test_expression.

    expressão[ ,... n ]

    É uma lista de expressões para testar uma correspondência. Todas as expressões devem ser do mesmo tipo que test_expression.

    Isso significa que apenas 1 coluna é permitida em ambos os lados. Mesmo uma lista de expressões é considerada como 1 tabela fictícia com 1 coluna para valores de expressão semelhantes a:

    SELECT exp FROM (values(exp1), (exp2), ...) as X(exp)

    Com 2 colunas você pode usar uma subconsulta com EXISTS :

    Select VendorName, VendorCity, VendorState 
FROM Vendors AS V1 
WHERE NOT EXISTS (
    SELECT 1 
    FROM Vendors AS V2 
    WHERE V2.VendorID <> V1.VendorID
        AND VendorCity = VendorCity
        AND VendorState = VendorState
)

    No geral esta subconsulta sempre retorna 0 linha ou 1 ou mais linhas com 1 pois ela só precisa saber se existe 1 ou mais linhas com a mesma Cidade e Estado. O valor não é importante porque ele olha apenas para o número de linhas (=> se EXISTE 1 ou mais linhas), portanto, o SELECT 1. O teste já foi feito na WHEREcláusula interna.

    Eu acho que VendorId é o PK. Um índice em VendorCity e VendorState ajudaria.

    Como você está aprendendo e se ainda não o conhece, também pode observar o uso de APPLY ( CROSS APPLY e OUTER APPLY ). Eu deixo você experimentar e brincar com ele.

    • 6

  2. Conforme declarado na outra resposta , o SQL Server atualmente não permite a sintaxe desejada. Outros produtos fazem e há uma solicitação de item Connect aqui:

    Adicione suporte para construtores de valor de linha padrão ANSI .

    Ainda é possível usar NOT INembora

    SELECT VendorName,
       VendorCity,
       VendorState
FROM   Vendors AS V1
WHERE  1 NOT IN (SELECT 1
                 FROM   Vendors AS V2
                 WHERE  V2.VendorCity = V1.VendorCity
                        AND V2.VendorState = V1.VendorState
                        AND V2.VendorID <> V1.VendorID);

    Isso fornece um plano semelhante, com um operador anti-semi-join, comoNOT EXISTS

    • 5

  3. Use CONCAT(...):

    Select VendorName, VendorCity, VendorState 
from Vendors AS V1 
where CONCAT( V1.VendorCity,V1.VendorState ) NOT IN (
    Select CONCAT( V2.VendorCity,V2.VendorState ) 
    FROM Vendors AS V2
    where V2.VendorID <> V1.VendorID
)

    Para abordar um comentário deixado por Martin Smith :

    Isso tem pelo menos um problema teórico em que (ABC,DEF)corresponderá (AB,CDEF)- não tenho certeza se existe algum par de cidade / estado do mundo real que possa causar esse problema. Também pode ser menos eficiente dependendo dos índices disponíveis.

    Os ABC | DEFanagramas não seriam um problema em um cenário real, pois não acho que existam combinações como ABCD | EF, por exemplo. O problema com os índices pode ser facilmente resolvido criando um índice contendo apenas as colunas "VendorCity, VendorState".

    Como alternativa, ele poderia criar sua própria tabela de hash para essas 2 colunas e armazenar o hash gerado como um campo extra na mesma tabela.

    Eu adoraria ver um benchmark comparando o desempenho das soluções fornecidas (incluindo a minha).

    • 1

relate perguntas