Início / dba / Perguntas / 21226
Accepted
Anonymous Maximus
Asked: 2012-07-21 05:12:01 +0800 CST

Por que os curingas nas instruções GROUP BY não funcionam?

  • 772

Estou tentando fazer a seguinte instrução SQL funcionar, mas recebo um erro de sintaxe:

SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.*

Aqui, A é uma tabela ampla com 40 colunas e gostaria de evitar listar cada nome de coluna na cláusula GROUP BY, se possível. Eu tenho muitas dessas tabelas nas quais tenho que executar uma consulta semelhante, então terei que escrever um procedimento armazenado. Qual é a melhor maneira de abordar isso?

Estou usando o MS SQL Server 2008.

sql-server sql-server-2008

3 respostas

  1. Best Answer

    GROUP BY A.*não é permitido em SQL.

    Você pode ignorar isso usando uma subconsulta onde você agrupa e, em seguida, une:

    SELECT A.*, COALESCE(B.cnt, 0) AS Count_B_Foo
FROM TABLE1 AS A
  LEFT JOIN 
      ( SELECT FKey, COUNT(foo) AS cnt
        FROM TABLE2 
        GROUP BY FKey
      ) AS B 
    ON A.PKey = B.FKey ;

    Existe um recurso no padrão SQL-2003 para permitir na SELECTlista, colunas que não estão na GROUP BYlista, desde que sejam funcionalmente dependentes delas. Se esse recurso tivesse sido implementado no SQL-Server, sua consulta poderia ser escrita como:

    SELECT A.*, COUNT(B.foo)
FROM TABLE1 A
LEFT JOIN TABLE2 B ON A.PKey = B.FKey
GROUP BY A.pk                          --- the Primary Key of table A

    Infelizmente, esse recurso ainda não foi implementado, nem mesmo na versão SQL-Server 2012 - e nem em nenhum outro SGBD que eu saiba. Exceto para o MySQL, que o possui, mas inadequadamente (inadequadamente como: a consulta acima funcionará, mas o mecanismo não fará nenhuma verificação de dependência funcional e outras consultas mal escritas mostrarão resultados errados e semi-aleatórios).

    Como @Mark Byers nos informou em um comentário, o PostgreSQL 9.1 adicionou um novo recurso projetado para essa finalidade. É mais restritivo do que a implementação do MySQL.

    • 34

  2. Além da solução alternativa do @ypercube, "digitar" nunca é uma desculpa para usar SELECT *. Escrevi sobre isso aqui e, mesmo com a solução alternativa, acho que sua SELECTlista ainda deve incluir os nomes das colunas - mesmo que haja um número enorme como 40.

    Para encurtar a história, você pode evitar digitar essas listas grandes clicando e arrastando o nó Colunas do objeto no Pesquisador de Objetos para a janela de consulta. A captura de tela mostra uma exibição, mas a mesma coisa pode ser feita para uma tabela.

    insira a descrição da imagem aqui

    Mas se você quiser ler sobre todas as razões pelas quais você deve se submeter a esse enorme esforço de arrastar um item alguns centímetros, leia meu post . :-)

    • 24

  3. Isso pode ser melhor resolvido com uma aplicação externa. (eliminando assim a necessidade do grupo por *)

    Se você está confiante em uma cardinalidade 1:n:

    SELECT A.*, B.CountFoo
FROM TABLE1 A
outer apply (select count(B.Foo) as CountFoo from TABLE2 B where A.PKey = B.FKey) B

    Se Table1 tiver duplicatas em PKey (embora dependendo do seu uso, você começa a ter uma lavagem em termos de complexidade versus apenas fazendo o grupo):

    ;with DupRows as 
(
    SELECT A.*
        ,MultipledCountFoo = B.CountFoo * Count(*) over (partition by A.PKey)
        ,RN = ROW_NUMBER() over (partition by A.PKey order by A.PKey)
    FROM TABLE1 A
    outer apply (select count(B.Foo) as CountFoo from TABLE2 B where A.PKey = B.FKey group by A.) B
)
Select * from DupRows where RN=1

    Evitando "Selecionar *"

    É importante observar que existem casos com CTEs em que a seleção da coluna física é feita anteriormente/posteriormente e a seleção de "todas as colunas" torna-se útil/prática e não afeta o plano de execução. Sim, você precisa EVENTUALMENTE limitar sua seleção de coluna e isso precisa acontecer antes de qualquer materialização (ou seja, união de todos), mas há usos aceitáveis ​​de select *.

    Suporte de versão

    FYI, parece que isso é suportado no sql server 2008 (r2?), Mas está ficando cada vez mais difícil pesquisar a documentação anterior a 2016. Também é difícil para mim dizer como a aplicação externa foi realizada em 2008. Posso confirmar que é preto -magic-awesome nas versões posteriores em comparação com consultas aninhadas equivalentes.

    • 0

relate perguntas