纪念一下写过的最长SQL

		SELECT '1'                               AS apply_type,
               GROUP_CONCAT(c.project_name)      AS project_name,
               GROUP_CONCAT(d.business_nickname) AS business_nickname,
               b.id                              AS apply_id,
               b.`status`,
               b.audit_node,
               e.`name`,
               b.submit_user_id,
               b.submit_time
        FROM (SELECT id, project_id FROM `oa_overtime_apply`) AS a
                 JOIN mysql.help_topic AS b ON b.help_topic_id < (char_length(a.project_id) -
                                                                     char_length(REPLACE(a.project_id, ',', '')) + 1)
                 RIGHT JOIN oa_overtime_apply b ON a.id = b.id
                 RIGHT JOIN oa_overtime_refer f ON b.id = f.apply_id
                 LEFT JOIN oa_project c
                           ON substring_index(substring_index(a.project_id, ',', b.help_topic_id + 1), ',', - 1) =
                              c.project_id
                 LEFT JOIN oa_business d ON c.project_business_id = d.business_id
                 LEFT JOIN oa_staff e ON b.submit_user_id = e.user_id
        WHERE b.STATUS != '0'
        AND f.user_id = #{userId}
        GROUP BY
            b.id
        UNION
        SELECT '2'                               AS apply_type,
               GROUP_CONCAT(c.project_name)      AS project_name,
               GROUP_CONCAT(d.business_nickname) AS business_nickname,
               b.id                              AS apply_id,
               b.`status`,
               b.audit_node,
               e.`name`,
               b.submit_user_id,
               b.submit_time
        FROM (SELECT id, project_id FROM `oa_purchase_apply`) AS a
                 JOIN mysql.help_topic AS b ON b.help_topic_id < (char_length(a.project_id) -
                                                                     char_length(REPLACE(a.project_id, ',', '')) + 1)
                 RIGHT JOIN oa_purchase_apply b ON a.id = b.id
                 RIGHT JOIN oa_purchase_refer f ON b.id = f.apply_id
                 LEFT JOIN oa_project c
                           ON substring_index(substring_index(a.project_id, ',', b.help_topic_id + 1), ',', - 1) =
                              c.project_id
                 LEFT JOIN oa_business d ON c.project_business_id = d.business_id
                 LEFT JOIN oa_staff e ON b.submit_user_id = e.user_id
        WHERE b.STATUS != '0'
        AND f.user_id = #{userId}
        GROUP BY
            b.id
        UNION
        SELECT '3'                               AS apply_type,
               GROUP_CONCAT(c.project_name)      AS project_name,
               GROUP_CONCAT(d.business_nickname) AS business_nickname,
               b.id                              AS apply_id,
               b.`status`,
               b.audit_node,
               e.`name`,
               b.submit_user_id,
               b.submit_time
        FROM (SELECT id, project_id FROM `oa_cost_apply`) AS a
                 JOIN mysql.help_topic AS b ON b.help_topic_id < (char_length(a.project_id) -
                                                                     char_length(REPLACE(a.project_id, ',', '')) + 1)
                 RIGHT JOIN oa_cost_apply b ON a.id = b.id
                 RIGHT JOIN oa_cost_refer f ON b.id = f.apply_id
                 LEFT JOIN oa_project c
                           ON substring_index(substring_index(a.project_id, ',', b.help_topic_id + 1), ',', - 1) =
                              c.project_id
                 LEFT JOIN oa_business d ON c.project_business_id = d.business_id
                 LEFT JOIN oa_staff e ON b.submit_user_id = e.user_id
        WHERE b.STATUS != '0'
        AND f.user_id = #{userId}
        GROUP BY
            b.id
        UNION
        SELECT '4'                               AS apply_type,
               GROUP_CONCAT(c.project_name)      AS project_name,
               GROUP_CONCAT(d.business_nickname) AS business_nickname,
               b.id                              AS apply_id,
               b.`status`,
               b.audit_node,
               e.`name`,
               b.submit_user_id,
               b.submit_time
        FROM (SELECT id, project_id FROM `oa_reimburse_apply`) AS a
                 JOIN mysql.help_topic AS b ON b.help_topic_id < (char_length(a.project_id) -
                                                                     char_length(REPLACE(a.project_id, ',', '')) + 1)
                 RIGHT JOIN oa_reimburse_apply b ON a.id = b.id
                 RIGHT JOIN oa_reimburse_refer f ON b.id = f.apply_id
                 LEFT JOIN oa_project c
                           ON substring_index(substring_index(a.project_id, ',', b.help_topic_id + 1), ',', - 1) =
                              c.project_id
                 LEFT JOIN oa_business d ON c.project_business_id = d.business_id
                 LEFT JOIN oa_staff e ON b.submit_user_id = e.user_id
        WHERE b.STATUS != '0'
        AND f.user_id = #{userId}
        GROUP BY
            b.id
        UNION
        SELECT '5'                               AS apply_type,
               GROUP_CONCAT(c.project_name)      AS project_name,
               GROUP_CONCAT(d.business_nickname) AS business_nickname,
               b.id                              AS apply_id,
               b.`status`,
               b.audit_node,
               e.`name`,
               b.submit_user_id,
               b.submit_time
        FROM (SELECT id, project_id FROM `oa_outwork_apply`) AS a
                 JOIN mysql.help_topic AS b ON b.help_topic_id < (char_length(a.project_id) -
                                                                     char_length(REPLACE(a.project_id, ',', '')) + 1)
                 RIGHT JOIN oa_outwork_apply b ON a.id = b.id
                 RIGHT JOIN oa_outwork_refer f ON b.id = f.apply_id
                 LEFT JOIN oa_project c
                           ON substring_index(substring_index(a.project_id, ',', b.help_topic_id + 1), ',', - 1) =
                              c.project_id
                 LEFT JOIN oa_business d ON c.project_business_id = d.business_id
                 LEFT JOIN oa_staff e ON b.submit_user_id = e.user_id
        WHERE b.STATUS != '0'
        AND f.user_id = #{userId}
        GROUP BY
            b.id
        UNION
        SELECT '6'                               AS apply_type,
               GROUP_CONCAT(c.project_name)      AS project_name,
               GROUP_CONCAT(d.business_nickname) AS business_nickname,
               b.id                              AS apply_id,
               b.`status`,
               b.audit_node,
               e.`name`,
               b.submit_user_id,
               b.submit_time
        FROM (SELECT id, project_id FROM `oa_trip_apply`) AS a
                 JOIN mysql.help_topic AS b ON b.help_topic_id < (char_length(a.project_id) -
                                                                     char_length(REPLACE(a.project_id, ',', '')) + 1)
                 RIGHT JOIN oa_trip_apply b ON a.id = b.id
                 RIGHT JOIN oa_trip_refer f ON b.id = f.apply_id
                 LEFT JOIN oa_project c
                           ON substring_index(substring_index(a.project_id, ',', b.help_topic_id + 1), ',', - 1) =
                              c.project_id
                 LEFT JOIN oa_business d ON c.project_business_id = d.business_id
                 LEFT JOIN oa_staff e ON b.submit_user_id = e.user_id
        WHERE b.STATUS != '0'
        AND f.user_id = #{userId}
        GROUP BY
            b.id
        UNION
        SELECT '7'                               AS apply_type,
               GROUP_CONCAT(c.project_name)      AS project_name,
               GROUP_CONCAT(d.business_nickname) AS business_nickname,
               b.id                              AS apply_id,
               b.`status`,
               b.audit_node,
               e.`name`,
               b.submit_user_id,
               b.submit_time
        FROM (SELECT id, project_id FROM `oa_settle_apply`) AS a
                 JOIN mysql.help_topic AS b ON b.help_topic_id < (char_length(a.project_id) -
                                                                     char_length(REPLACE(a.project_id, ',', '')) + 1)
                 RIGHT JOIN oa_settle_apply b ON a.id = b.id
                 RIGHT JOIN oa_settle_refer f ON b.id = f.apply_id
                 LEFT JOIN oa_project c
                           ON substring_index(substring_index(a.project_id, ',', b.help_topic_id + 1), ',', - 1) =
                              c.project_id
                 LEFT JOIN oa_business d ON c.project_business_id = d.business_id
                 LEFT JOIN oa_staff e ON b.submit_user_id = e.user_id
        WHERE b.STATUS != '0'
        AND f.user_id = #{userId}
        GROUP BY
            b.id
        ORDER BY
            submit_time DESC