无法弄清楚为什么子查询返回超过 1 行

Question

i具有以下数据，该数据也位于 sql fiddle 中。

    CREATE TABLE completework
    (`id` int, `workerid` int, `checkerid` int);
    CREATE TABLE worker
    (`id` int, `name` varchar(20));
    INSERT INTO completework
        (`id`, `workerid`, `checkerid`)
    VALUES
        (1, 2, 4),
        (2, 3, 5),
        (3, 4, 1),
        (4, 5, 2),
        (5, 1, 3),
        (6, 2, 4),
        (7, 3, 5),
        (8, 4, 1),
        (9, 5, 2),
        (10, 1, 3),
        (11, 2, 4),
        (12, 3, 5),
        (13, 4, 1),
        (14, 5, 2);
    INSERT INTO worker
        (`id`, `name`)
    VALUES
    (1, 'Anthony'),
    (2, 'Bill'),
    (3, 'Claire'),
    (4, 'Daniel'),
    (5, 'Emma');

我想在工人是工人或检查员的情况下返回结果，所以我认为我需要将这两个列结合在一起，但是当我尝试这样做时，我会收到错误

Subquery returns more than 1 row

我尝试了以下

    select
    workerid as wid,
    wn.name
    from  completework
    inner join worker wn on completework.workerid=wn.id 
    group by wid

上面的工作列

    select
    checkerid as cid,
    wn.name
    from  completework
    inner join worker wn on completework.checkerid=wn.id 
    group by cid

上面的CheckerId列

    SELECT DISTINCT workedonid FROM (
      SELECT checkerid workedonid FROM completework  
      UNION 
      SELECT workerid workedonid FROM completework
    )c
    ORDER BY workedonid ASC

以上从任一列获得ID

    select
        (
            SELECT DISTINCT workedonid FROM (
              SELECT checkerid workedonid FROM completework  
              UNION 
              SELECT workerid workedonid FROM completework
            )c
        ) a,
    wn.name
    from  completework
    inner join worker wn on completework.checkerid=wn.id 
    group by a

在上面的我试图将上述工作用于子查询，但是遇到了错误，我该如何解决？

Answer 1

尝试以下：

SELECT DISTINCT w.id AS wid, w.name
FROM worker w
INNER JOIN completework cw ON w.id IN (cw.workerid, cw.checkerid);

检查此 SQL小提琴演示

输出

| WID |    NAME |
|-----|---------|
|   2 |    Bill |
|   4 |  Daniel |
|   3 |  Claire |
|   5 |    Emma |
|   1 | Anthony |