Início / dba / Perguntas / 331688
Accepted
Md. A. Apu
Asked: 2023-09-29 02:38:15 +0800 CST

Como usar a coluna agregada na cláusula where ao usar group by

  • 772

Eu tenho a seguinte tabela

CREATE TABLE `books` (
  `book_id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) DEFAULT NULL,
  `author_fname` varchar(100) DEFAULT NULL,
  `author_lname` varchar(100) DEFAULT NULL,
  `released_year` int(11) DEFAULT NULL,
  `stock_quantity` int(11) DEFAULT NULL,
  `pages` int(11) DEFAULT NULL,
  PRIMARY KEY (`book_id`)
)

Tentando descobrir o ano de lançamento do primeiro e último livro do autor apenas para autores que possuem mais de um livro. A seguir está a consulta

SELECT author_lname,
       MIN(released_year) first_release,
       MAX(released_year) last_release,
       COUNT(*) book_count,
       MAX(pages) max_page_count
FROM books
GROUP BY author_lname
ORDER BY book_count DESC;

Mas não posso usar a cláusula book_countin where, então posso fazer. book_count > 1 Estou procurando uma explicação de por que isso não é possível e como obter o resultado esperado.

mysql

1 respostas

  1. Best Answer

    Por que não é possível

    A WHEREcláusula aplica condições para filtrar linhas, uma linha por vez. A expressão na WHEREcláusula pode fazer referência apenas às colunas de uma linha unida. Ou seja, qualquer coluna de múltiplas tabelas sendo unidas, mas não quaisquer aliases para colunas formadas por expressões na lista de seleção, e não quaisquer aliases para colunas formadas por agregações em múltiplas linhas.

    Apesar da SELECTcláusula aparecer primeiro na sintaxe da consulta, as expressões da lista de seleção são avaliadas depois que as linhas são filtradas pelas condições da WHEREcláusula.

    Da mesma forma, as expressões agregadas na lista de seleção são aplicadas apenas ao subconjunto de linhas que passam as condições da WHEREcláusula. Portanto, a WHEREcláusula deve filtrar as linhas primeiro.

    Como conseguir o que você deseja

    Alguns métodos podem resolver isso.

    Uma é usar uma subconsulta de tabela derivada. A consulta externa pode aplicar condições em sua WHEREcláusula para filtrar as linhas produzidas pela subconsulta, depois que a subconsulta faz sua agregação e aliases e assim por diante.

    SELECT *
FROM (
    SELECT author_lname,
       MIN(released_year) first_release,
       MAX(released_year) last_release,
       COUNT(*) book_count,
       MAX(pages) max_page_count
    FROM books
    GROUP BY author_lname
) AS derived
WHERE derived.book_count > 1
ORDER BY book_count DESC;

    Não deve fazer diferença ORDER BY dentro de uma subconsulta, a menos que a subconsulta também use LIMIT.

    Outro método é usar HAVINGwhich WHEREfiltra linhas, mas funciona após a agregação ser concluída e as expressões da lista de seleção terem sido avaliadas. Pense nisso HAVINGcomo grupos de filtragem , enquanto filtra linhasWHERE individuais .

    SELECT author_lname,
       MIN(released_year) first_release,
       MAX(released_year) last_release,
       COUNT(*) book_count,
       MAX(pages) max_page_count
FROM books
GROUP BY author_lname
HAVING book_count > 1
ORDER BY book_count DESC;

    Qual método é melhor?

    Prefiro usar HAVINGsempre que possível, porque a tabela derivada pode precisar criar uma tabela temporária para armazenar os resultados, e as tabelas temporárias podem prejudicar o desempenho.

    Mas pode haver casos em que o uso de uma subconsulta de tabela derivada seja necessário de qualquer maneira. Talvez você queira associar o resultado da subconsulta a outra tabela, por exemplo.

    • 3

relate perguntas