mysql - 无法弄清楚为什么子查询返回多于 1 行

Question

我有以下数据，这些数据也在 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

上面只是获取了workerid列

    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 FIDDLE DEMO

输出

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