复杂sql记录

最后更新时间：2025年03月27日

字段存储，设计必须这么存

fieldis_showis_dup_key
xxx00
xxx10
xxx01

sql查询时压缩，c_rule_md5与业务表关联，这是query1

SELECT
    c_rule_md5,
    field_id,
    MAX(is_show) AS is_show,
    MAX(is_dup_key) AS is_dup_key
FROM
    t_rel_complicated_rules_fields trcrf
WHERE
    trcrf.is_dup_key != 1

字段信息和字段表和表基本信息表连接查询，获取字段所属表名称，字段名称，字段中文名等，这是query5

SELECT
    ttf.column_name_cn,
    ttf.column_name,
    ttbi.table_name,
    ttbi.table_schema,
    ttbi.table_schema_cn,
    ttbi.table_name_cn,
    query1.c_rule_md5,
    query1.is_show,
    query1.is_dup_key,
    ttf.id AS field_id 
FROM
    (
    SELECT
        c_rule_md5,
        field_id,
        MAX( is_show ) AS is_show,
        MAX( is_dup_key ) AS is_dup_key 
    FROM
        t_rel_complicated_rules_fields trcrf 
    WHERE
        trcrf.is_dup_key != 1 
    GROUP BY
        c_rule_md5,
        field_id 
    ) query1
    LEFT JOIN t_table_field ttf ON query1.field_id = ttf.id
    LEFT JOIN t_table_base_info ttbi ON ttbi.table_schema = ttf.table_schema 
    AND ttbi.table_name = ttf.table_name 
WHERE
    ttbi.table_schema = 'db_enterprise' 
    AND ttbi.table_name = 't_address' 
    AND query1.field_id = 396 
    )

查最新一条报告，这是query3

SELECT
    tcrr.id,
    tcrr.rule_md5,
    tcrr.is_alarming,
    tcrr.create_time AS last_execute_time 
FROM
    t_complicated_rule_report tcrr
    JOIN ( SELECT rule_md5, MAX( create_time ) AS max_create_time FROM t_complicated_rule_report GROUP BY rule_md5 ) query2 ON tcrr.rule_md5 = query2.rule_md5 
    AND tcrr.create_time = query2.max_create_time

最新一条报告和主表连接，tcrc与query3左连接，生成query4

SELECT
    tcrc.id AS id,
    tcrc.rule_name,
    tcrc.rule_md5,
    tcrc.rule_type_id AS rule_type_id,
    tcrc.create_user,
    tcrc.create_time,
    tcrc.cron_type,
    tcrc.rule_status AS is_online,
    tcrc.rule_update_time,
    tcrc.online_approve_id,
    query3.is_alarming AS is_alarming,
    query3.last_execute_time,
    tcrc.try_running_percent 
FROM
    t_complicated_rule_config tcrc
    LEFT JOIN (
    SELECT
        tcrr.id,
        tcrr.rule_md5,
        tcrr.is_alarming,
        tcrr.create_time AS last_execute_time 
    FROM
        t_complicated_rule_report tcrr
        JOIN ( SELECT rule_md5, MAX( create_time ) AS max_create_time FROM t_complicated_rule_report GROUP BY rule_md5 ) query2 ON tcrr.rule_md5 = query2.rule_md5 
        AND tcrr.create_time = query2.max_create_time 
    ) query3 ON query3.rule_md5 = tcrc.rule_md5 
    ) query4

query4与query5进行内连接，生成query6

( 
    SELECT
    query4.*,
    query5.column_name_cn,
    query5.column_name,
    query5.table_name,
    query5.table_schema,
    query5.table_schema_cn,
    query5.table_name_cn,
    query5.is_show,
    query5.is_dup_key,
    query5.field_id 
    FROM
        (...) query4
        INNER JOIN 
         (...) query5 ON query4.rule_md5 = query5.c_rule_md5 
    ) query6

mysql 5.7没有row_number()，需要通过mysql变量生成row_number

( SELECT @row_number := 0, @current_md5 := '', @is_show_sum := 0 ) AS vars

使用变量 @row_number、@current_md5 和 @is_show_sum 来实现分组和过滤逻辑，生成query7，在这里的目的是生成每个分组内的row_number并判断每个分组内的is_show是否全部为0；注意query6.is_show DESC，可以确保分组内存在is_show=1的值在前，则存在is_show=1分组内每行的is_show_sum>0；

SELECT
    query6.*,
    @row_number :=IF( @current_md5 = query6.rule_md5, @row_number + 1, 1 ) AS row_number,
    @is_show_sum := IF( @current_md5 = query6.rule_md5, @is_show_sum + query6.is_show, query6.is_show ) AS is_show_sum,
    @current_md5 := query6.rule_md5 
FROM
    (...) query6,
    ( SELECT @row_number := 0, @current_md5 := '', @is_show_sum := 0 ) AS vars 
ORDER BY
    query6.rule_md5 ASC,
    query6.is_show DESC 
    ) query7

如果分组内 is_show 有一个不为0，则保留整个分组。如果分组内 is_show 全为0，则只保留分组中的第一行（即 row_number = 1）。

SELECT
    * 
FROM
    query7 
WHERE
    (
    query7.is_show_sum > 0 
    OR ( query7.is_show_sum = 0 AND query7.row_number = 1 ))

总体：

SELECT
    query7.*
from
    (
    select
        query6.*,
        @row_number := IF(@current_md5 = query6.rule_md5,
        @row_number + 1,
        1) AS row_number,
        @is_show_sum := IF(@current_md5 = query6.rule_md5,
        @is_show_sum + query6.is_show,
        query6.is_show) AS is_show_sum,
        @current_md5 := query6.rule_md5
    from
        (
        SELECT
            query4.*,
            query5.column_name_cn,
            query5.column_name,
            query5.table_name,
            query5.table_schema,
            query5.table_schema_cn,
            query5.table_name_cn,
            query5.is_show,
            query5.is_dup_key,
            query5.field_id
        FROM
            (
            SELECT
                tcrc.id as id,
                tcrc.rule_name,
                tcrc.rule_md5,
                tcrc.rule_type_id as rule_type_id,
                tcrc.create_user,
                tcrc.create_time,
                tcrc.cron_type,
                tcrc.rule_status as is_online,
                tcrc.rule_update_time,
                tcrc.online_approve_id,
                query3.is_alarming as is_alarming,
                query3.last_execute_time,
                tcrc.try_running_percent
            FROM
                t_complicated_rule_config tcrc
            LEFT JOIN (
                SELECT
                    tcrr.id,
                    tcrr.rule_md5,
                    tcrr.is_alarming,
                    tcrr.create_time as last_execute_time
                FROM
                    t_complicated_rule_report tcrr
                JOIN (
                    SELECT
                        rule_md5,
                        MAX(create_time) AS max_create_time
                    FROM
                        t_complicated_rule_report
                    GROUP BY
                        rule_md5 ) query2 ON
                    tcrr.rule_md5 = query2.rule_md5
                    AND tcrr.create_time = query2.max_create_time ) query3 ON
                query3.rule_md5 = tcrc.rule_md5 ) query4
        INNER JOIN (
            SELECT
                ttf.column_name_cn,
                ttf.column_name,
                ttbi.table_name,
                ttbi.table_schema,
                ttbi.table_schema_cn,
                ttbi.table_name_cn,
                query1.c_rule_md5,
                query1.is_show,
                query1.is_dup_key,
                ttf.id as field_id
            FROM
                (
                SELECT
                    c_rule_md5,
                    field_id,
                    MAX(is_show) AS is_show,
                    MAX(is_dup_key) AS is_dup_key
                FROM
                    t_rel_complicated_rules_fields trcrf
                WHERE
                    trcrf.is_dup_key != 1
                GROUP BY
                    c_rule_md5,
                    field_id ) query1
            LEFT JOIN t_table_field ttf ON
                query1.field_id = ttf.id
            LEFT JOIN t_table_base_info ttbi ON
                ttbi.table_schema = ttf.table_schema
                AND ttbi.table_name = ttf.table_name
            WHERE
                ttbi.table_schema = 'db_enterprise'
                AND ttbi.table_name = 't_address'
                AND query1.field_id = 396 ) query5 ON
            query4.rule_md5 = query5.c_rule_md5 ) query6,
        (
        SELECT
            @row_number := 0,
            @current_md5 := '',
            @is_show_sum := 0) AS vars
    order by
        query6.rule_md5 asc,
        query6.is_show desc 
        ) query7
where
    (query7.is_show_sum > 0
        OR (query7.is_show_sum = 0
            AND query7.row_number = 1))
    AND rule_type_id = 4
    AND query7.is_online = 1
    AND (query7.is_alarming = 0
        or query7.is_alarming is NULL)
    AND query7.try_running_percent = 100

声明：Hello World|版权所有，违者必究|如未注明，均为原创|本网站采用BY-NC-SA协议进行授权

转载：转载请注明原文链接 - 复杂sql记录

我的朋友，理论是灰色的，而生活之树是常青的！