代码之家  ›  专栏  ›  技术社区  ›  Afsan Abdulali Gujarati

MySQL:使用IF条件对重复ID进行分组

  •  1
  • Afsan Abdulali Gujarati  · 技术社区  · 7 年前

    RaterID | Name  |
    =================
    rater_1 |Katty  |
    rater_2 |Batty  |
    rater_3 |Ratty  |
    rater_4 |Shatty |
    rater_5 |Patty  |
    

    还有另一个表tblAuthorizedRater(其中包含将哪个评分员分配给哪个主题的数据),其样本数据如下:

    TopicID | RaterID |
    ===================
    topic_1 | rater_1 |
    topic_1 | rater_2 |
    topic_1 | rater_3 |
    topic_2 | rater_2 |
    topic_2 | rater_3 |
    topic_3 | rater_1 |
    topic_3 | rater_2 |
    topic_6 | rater_1 |
    

    因此,当我查找允许对主题_1进行评分的评分者名单时,我希望得到以下数据:

    RaterID | Authorized|
    =====================
    rater_1 | 1         |
    rater_2 | 1         |
    rater_3 | 1         |
    rater_4 | 0         |
    rater_5 | 0         |
    

    仅供参考,以下是我目前正在尝试的问题:

    select
        tr.RaterID,
        IF(tar.TopicID = 'topic_1', 1, 0) as Authorized 
    from tblRater as tr 
    left join tblAuthorizedRater as tar
    on tr.RaterID = tar.RaterID;
    

    1 回复  |  直到 7 年前
        1
  •  2
  •   Barmar    7 年前

    topicID = 'topic_1' ON tblAuthorizedRater NULL .

    SELECT 
        tr.RaterID,
        tar.topicID IS NOT NULL AS Authorized
    FROM tblRater AS tr
    LEFT JOIN tblAuthorizedRater AS tar
    ON tr.RaterID = tar.RaterID AND tar.TopicID = 'topic_1'
    

    DEMO