mysql - 当条件基于另一个 SELECT 的结果时进行 SELECT

Question

查询 #1 按预期工作，但“smf_user_points_log.id_school_year”不会每次都是“7”。该值(而不是 7)必须是查询 #2 的结果。

查询#1:

SELECT smf_user_points_log.id_member, smf_members.usertitle
        FROM smf_user_points_log
        INNER JOIN smf_members
        ON smf_members.id_member=smf_user_points_log.id_member
        WHERE smf_user_points_log.id_school_year = 7 AND smf_user_points_log.points >= 50 AND smf_members.date_registered < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))

查询#2:

SELECT id_school_year FROM smf_settings

我应该如何重写查询 #1 才能获得相同的结果？我尝试了各种连接但没有成功。

Answer 1

您可以将 IN 与子查询一起使用:

SELECT l.id_member, 
    m.usertitle
FROM smf_user_points_log l
INNER JOIN smf_members m
    ON m.id_member=l.id_member
WHERE l.id_school_year in (SELECT id_school_year FROM smf_settings)
    AND l.points >= 50 
    AND m.date_registered < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 30 DAY))

此外，请考虑使用简短且有意义的别名来提高查询的可读性。