求助：编写引用元值的SQL查询，现有代码需协助完善

嘿，我看你在写WordPress里拉取Ninja Forms（nf_sub应该是它的提交类型）提交数据的SQL，这段代码已经有雏形了，我帮你补全并优化一下，顺便解决几个潜在的小问题：

基础完善版（保留你原有的子查询结构） #

$sql = "SELECT * 
FROM (
    SELECT 
        p.ID AS post_id,
        p.post_date,
        m1.meta_value AS project_name,
        m2.meta_value AS access_key,
        m3.meta_value AS seq_number
    FROM wpha_posts p
    JOIN wpha_postmeta m1 ON m1.post_id = p.ID AND m1.meta_key = '_field_5'
    JOIN wpha_postmeta m2 ON m2.post_id = p.ID AND m2.meta_key = '_field_7'
    JOIN wpha_postmeta m3 ON m3.post_id = p.ID AND m3.meta_key = '_seq_num'
    WHERE 
        p.post_type = 'nf_sub' 
        AND p.post_author = " . (int)$profile_id . "
        -- 这里补充你没写完的条件，比如过滤已发布的提交：
        -- AND p.post_status = 'publish'
) AS submission_data";

关键调整点： #

• 给wpha_posts表加了别名p，彻底避免ID字段的歧义（原代码里直接用ID会让数据库混淆是子查询的别名还是原表的主键）
• 给子查询加了必填的别名submission_data，MySQL要求所有子查询必须有别名才能被外层SELECT调用
• 给$profile_id强制转成整数(int)$profile_id，防止SQL注入风险（如果你的$profile_id是字符串类型，直接拼接会有安全隐患）
• 把原查询里的m1.post_id as ID改成p.ID AS post_id，语义更清晰，避免和元表的post_id混淆

简化高效版（去掉多余的子查询） #

如果你的需求只是拉取这几个字段，外层的SELECT *其实是多余的，直接查询更高效：

$sql = "SELECT 
        p.ID,
        p.post_date,
        m1.meta_value AS project_name,
        m2.meta_value AS access_key,
        m3.meta_value AS seq_number
    FROM wpha_posts p
    JOIN wpha_postmeta m1 ON m1.post_id = p.ID AND m1.meta_key = '_field_5'
    JOIN wpha_postmeta m2 ON m2.post_id = p.ID AND m2.meta_key = '_field_7'
    JOIN wpha_postmeta m3 ON m3.post_id = p.ID AND m3.meta_key = '_seq_num'
    WHERE 
        p.post_type = 'nf_sub' 
        AND p.post_author = " . (int)$profile_id . "
        -- 可以在这里补充任何你需要的过滤条件，比如日期范围：
        -- AND p.post_date >= '2024-01-01'
";

兼容缺失元字段版（用LEFT JOIN） #

如果有些表单提交可能缺少其中某个元字段（比如没填_field_5），用JOIN会直接过滤掉这些记录。如果要保留所有符合条件的提交，哪怕元字段缺失，可以改成LEFT JOIN，再用COALESCE设置默认值：

$sql = "SELECT 
        p.ID,
        p.post_date,
        COALESCE(m1.meta_value, '未填写') AS project_name,
        COALESCE(m2.meta_value, '未生成') AS access_key,
        COALESCE(m3.meta_value, '0') AS seq_number
    FROM wpha_posts p
    LEFT JOIN wpha_postmeta m1 ON m1.post_id = p.ID AND m1.meta_key = '_field_5'
    LEFT JOIN wpha_postmeta m2 ON m2.post_id = p.ID AND m2.meta_key = '_field_7'
    LEFT JOIN wpha_postmeta m3 ON m3.post_id = p.ID AND m3.meta_key = '_seq_num'
    WHERE 
        p.post_type = 'nf_sub' 
        AND p.post_author = " . (int)$profile_id . "
";

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