如何将MySQL 8含OVER()的SQL查询降级适配MySQL 5.7

针对查询1的改写 #

原查询（修正temembers笔误后）：

SELECT SUM(COUNT(application.email)*members.referral_bonus) OVER() AS total_count 
FROM application
LEFT JOIN members ON members.re_link=application.aff_code
WHERE application.re_status='1' AND MONTH(application.completed_paydate)='$month'

MySQL 5.7不支持窗口函数，这里SUM(...) OVER()的作用是计算全局聚合总和。由于原查询没有GROUP BY，可以直接用普通聚合函数实现：

SELECT SUM(IF(members.referral_bonus IS NOT NULL, COUNT(application.email)*members.referral_bonus, 0)) AS total_count
FROM application
LEFT JOIN members ON members.re_link=application.aff_code
WHERE application.re_status='1' AND MONTH(application.completed_paydate)='$month'

如果原查询实际需要先按aff_code分组再求和，改用子查询实现：

SELECT SUM(group_total) AS total_count
FROM (
    SELECT COUNT(application.email) * members.referral_bonus AS group_total
    FROM application
    LEFT JOIN members ON members.re_link=application.aff_code
    WHERE application.re_status='1' AND MONTH(application.completed_paydate)='$month'
    GROUP BY application.aff_code, members.referral_bonus
) AS sub_query

针对查询2的改写 #

原查询：

SELECT aff_members.app_full_name, COUNT(application.email) AS NumberOfRe, 
       SUM(COUNT(application.email)*members.re_bonus) OVER(PARTITION BY application.aff_code) AS total_count 
FROM application
LEFT JOIN members ON members.re_link=application.aff_code
WHERE application.re_status='1' AND MONTH(application.completed_paydate)<='$last_month'
GROUP BY app_email

这里OVER(PARTITION BY application.aff_code)用于计算每个aff_code分组的总和，在MySQL 5.7中推荐用预计算分组总和再关联的方式实现（性能优于关联子查询）：

SELECT 
    aff_members.app_full_name, 
    COUNT(application.email) AS NumberOfRe,
    aff_totals.total_count
FROM application
LEFT JOIN members ON members.re_link=application.aff_code
-- 预计算每个aff_code对应的总和
LEFT JOIN (
    SELECT 
        a2.aff_code,
        SUM(COUNT(a2.email) * m2.re_bonus) AS total_count
    FROM application a2
    LEFT JOIN members m2 ON m2.re_link = a2.aff_code
    WHERE a2.re_status='1' AND MONTH(a2.completed_paydate)<='$last_month'
    GROUP BY a2.aff_code
) AS aff_totals ON aff_totals.aff_code = application.aff_code
WHERE application.re_status='1' AND MONTH(application.completed_paydate)<='$last_month'
-- MySQL 5.7要求GROUP BY包含所有非聚合字段
GROUP BY app_email, aff_members.app_full_name, application.aff_code, aff_totals.total_count

如果偏好关联子查询的写法，也可以这样写：

SELECT 
    aff_members.app_full_name, 
    COUNT(application.email) AS NumberOfRe,
    (
        SELECT SUM(COUNT(a2.email) * m2.re_bonus)
        FROM application a2
        LEFT JOIN members m2 ON m2.re_link = a2.aff_code
        WHERE a2.re_status='1' 
          AND MONTH(a2.completed_paydate)<='$last_month'
          AND a2.aff_code = application.aff_code
        GROUP BY a2.aff_code
    ) AS total_count
FROM application
LEFT JOIN members ON members.re_link=application.aff_code
WHERE application.re_status='1' AND MONTH(application.completed_paydate)<='$last_month'
GROUP BY app_email, aff_members.app_full_name, application.aff_code

注意：原查询的GROUP BY app_email在MySQL 5.7的ONLY_FULL_GROUP_BY模式下会报错，必须把aff_members.app_full_name和application.aff_code也加入GROUP BY。

内容的提问来源于stack exchange，提问作者Talamproxy