MySQL -子查询排序MySQL

Question

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时，完全选择最小值，而post_type、post_status则不计算分组。

Answer 1

与混合聚集列和子查询不同，yuo应该使用条件聚合来筛选所需的行，避免子查询。

通过这种方式，您可以使用组by而不与与子查询相关的差异聚集级别冲突。

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


    , MIN (CASE WHEN post_text_p_length !='' THEN post_text_p_length ELSE 100000 END) 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

    , MIN( CASE WHEN post_text_p_length_characters_without_spaces !='' THEN post_text_p_length_characters_without_spaces ELSE 100000 END)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


    , MIN( CASE WHEN post_text_p_length_characters !='' THEN post_text_p_length_characters ELSE 100000 END ) AS min_text_p_length_characters
    .....
    ....
    , MIN ( CASE WHEN  post_text_h3_length_characters !='' THEN post_text_h3_length_characters ELSE 100000 END)
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";

其中，100000只是一个虚拟值，用于在情况匹配中避免空值的最小值。