由于内部连接，两次显示mysql记录

cvxl0en2 于 2021-06-17 发布在 Mysql

关注(0)|答案(3)|浏览(319)

在下面的问题（导师）是13，显示我26，而（学校主管）是5，显示我10，这是错误的。正是因为有两个证据表明我的价值观，因为有两个证据表明导师和学校主管的价值观是双重的。请帮帮我。
查询：

select t.c_id,t.province,t.district,t.cohort,t.duration,t.venue,t.v_date,t.review_level, t.activity, 
        SUM(CASE WHEN pr.p_association = "Mentor" THEN 1 ELSE 0 END) as Mentor, 
        SUM(CASE WHEN pr.p_association = "School Supervisor" THEN 1 ELSE 0 END) as SchoolSupervisor,
        (CASE WHEN count(file_id) > 0 THEN "Yes" ELSE "No" END) as evidence
        FROM review_m t , review_attndnce ra 
        LEFT JOIN participant_registration AS pr ON pr.p_id = ra.p_id 
        LEFT JOIN review_files AS rf ON rf.training_id  = ra.c_id
        WHERE 1=1 AND t.c_id = ra.c_id
        group by t.c_id, ra.c_id order by t.c_id desc

在此处输入图像描述

sql mysql

来源：https://stackoverflow.com/questions/53736839/shows-mysql-records-twice-because-of-inner-joining

3条答案

按热度按时间

tkclm6bt1#

尝试将其添加到查询的where部分

AND pr.p_id IS NOT NULL AND rf.training_id IS NOT NULL

赞(0）回复(0）举报 2021-06-18

nc1teljy2#

您可以在单独的子查询中执行聚合，然后加入到该子查询：

SELECT
    t.c_id,
    t.province,
    t.district,
    t.cohort,
    t.duration,
    t.venue,
    t.v_date,
    t.review_level,
    t.activity,
    pr.Mentor,
    pr.SchoolSupervisor,
    rf.evidence
FROM review_m t
INNER JOIN review_attndnce ra
    ON t.c_id = ra.c_id
LEFT JOIN
(
    SELECT
        p_id,
        COUNT(CASE WHEN p_association = 'Mentor' THEN 1 END) AS Mentor,
        COUNT(CASE WHEN p_association = 'School Supervisor' THEN 1 END) AS SchoolSupervisor,
    FROM participant_registration
    GROUP BY p_id
) pr
    ON pr.p_id = ra.p_id
LEFT JOIN
(
    SELECT
        training_id,
        CASE WHEN COUNT(file_id) > 0 THEN 'Yes' ELSE 'No' END AS evidence
    FROM review_files
    GROUP BY training_id
) rf
    ON rf.training_id = ra.c_id
ORDER BY
    t.c_id DESC;

请注意，这还修复了查询的另一个问题，即您选择的许多列没有出现在 GROUP BY 条款。在此重构下，当前select没有任何问题，因为聚合发生在单独的子查询中。

赞(0）回复(0）举报 2021-06-18

wixjitnu3#

您可以按pr.p\u id添加组，以删除其中的重复记录。从那以后，这群人 pr 到目前为止不存在，可能有多个相同p\u id的记录 ra ```
group by t.c_id, ra.c_id, pr.p_id order by t.c_id desc

赞(0）回复(0）举报 2021-06-18