Группировка подзапроса

118

28 ноября 2021, 03:00

        SELECT post_type, post_status, COUNT(post_id) AS posts, SUM(post_word_count) AS word_count, SUM(post_tag_p_count) AS text_p_count, SUM(post_text_p_length) AS text_p_length, MAX(post_text_p_length) AS max_text_p_length, (SELECT MIN(post_text_p_length) FROM $table_name_posts WHERE post_text_p_length !='') AS min_text_p_length, SUM(post_text_p_length_characters_without_spaces) AS text_p_length_characters_without_spaces, MAX(post_text_p_length_characters_without_spaces) AS max_text_p_length_characters_without_spaces, (SELECT MIN(post_text_p_length_characters_without_spaces) FROM $table_name_posts WHERE post_text_p_length_characters_without_spaces !='') AS min_text_p_length_characters_without_spaces, SUM(post_text_p_length_characters) AS text_p_length_characters, MAX(post_text_p_length_characters) AS max_text_p_length_characters, (SELECT MIN(post_text_p_length_characters) FROM $table_name_posts WHERE post_text_p_length_characters !='') AS min_text_p_length_characters, SUM(post_tag_h1_count) AS text_h1_count, SUM(post_text_h1_length) AS text_h1_length, MAX(post_text_h1_length) AS max_text_h1_length, (SELECT MIN(post_text_h1_length) FROM $table_name_posts WHERE post_text_h1_length !='') AS min_text_h1_length, SUM(post_text_h1_length_characters_without_spaces) AS text_h1_length_characters_without_spaces, MAX(post_text_h1_length_characters_without_spaces) AS max_text_h1_length_characters_without_spaces, (SELECT MIN(post_text_h1_length_characters_without_spaces) FROM $table_name_posts WHERE post_text_h1_length_characters_without_spaces !='') AS min_text_h1_length_characters_without_spaces, SUM(post_text_h1_length_characters) AS text_h1_length_characters, MAX(post_text_h1_length_characters) AS max_text_h1_length_characters, (SELECT MIN(post_text_h1_length_characters) FROM $table_name_posts WHERE post_text_h1_length_characters !='') AS min_text_h1_length_characters, SUM(post_tag_h2_count) AS text_h2_count, SUM(post_text_h2_length) AS text_h2_length, MAX(post_text_h2_length) AS max_text_h2_length, (SELECT MIN(post_text_h2_length) FROM $table_name_posts WHERE post_text_h2_length !='') AS min_text_h2_length, SUM(post_text_h2_length_characters_without_spaces) AS text_h2_length_characters_without_spaces, MAX(post_text_h2_length_characters_without_spaces) AS max_text_h2_length_characters_without_spaces, (SELECT MIN(post_text_h2_length_characters_without_spaces) FROM $table_name_posts WHERE post_text_h2_length_characters_without_spaces !='') AS min_text_h2_length_characters_without_spaces, SUM(post_text_h2_length_characters) AS text_h2_length_characters, MAX(post_text_h2_length_characters) AS max_text_h2_length_characters, (SELECT MIN(post_text_h2_length_characters) FROM $table_name_posts WHERE post_text_h2_length_characters !='') AS min_text_h2_length_characters, SUM(post_tag_h3_count) AS text_h3_count, SUM(post_text_h3_length) AS text_h3_length, MAX(post_text_h3_length) AS max_text_h3_length, (SELECT MIN(post_text_h3_length) FROM $table_name_posts WHERE post_text_h3_length !='') AS min_text_h3_length, SUM(post_text_h3_length_characters_without_spaces) AS text_h3_length_characters_without_spaces, MAX(post_text_h3_length_characters_without_spaces) AS max_text_h3_length_characters_without_spaces, (SELECT MIN(post_text_h3_length_characters_without_spaces) FROM $table_name_posts WHERE post_text_h3_length_characters_without_spaces !='') AS min_text_h3_length_characters_without_spaces, SUM(post_text_h3_length_characters) AS text_h3_length_characters, MAX(post_text_h3_length_characters) AS max_text_h3_length_characters, (SELECT MIN(post_text_h3_length_characters) FROM $table_name_posts WHERE post_text_h3_length_characters !='') AS min_text_h3_length_characters
        FROM $table_name_posts
        WHERE (post_status = 'publish' OR post_status = 'draft' OR post_status = 'future')
        GROUP BY post_type, post_status
        ORDER BY word_count DESC

Проблема с группировкой подзапроса, при выборе MIN выбирается минимальное значение вообще, не отрабатывает GROUP BY post_type, post_status

Answer 1

У вас значения подзапросов никак не зависят от основной выборки. В текущий момент они выполняются как отдельные запросы, а запрос без group by выдает единственный результат по всей таблице в целом. При таком использовании они должны быть коррелированными, т.е. иметь в части WHERE условия выборки зависящие от значений из основной таблицы запроса.

Но учитывая, что подзапросы обращаются к той же таблице, что и основной запрос, они вообще не нужны. Надо лишь получать минимумы для части строк основной выборки, т.е. по условию, примерно так:

SELECT post_type, post_status, ...,
       MIN(case when post_text_p_length !='' then post_text_p_length end) AS min_text_p_length
  FROM $table_name_posts
 WHERE (post_status = 'publish' OR post_status = 'draft' OR post_status = 'future')
 GROUP BY post_type, post_status
 ORDER BY word_count DESC