mysql - 从引用表中列出所有员工的姓名？

Question

我有一个表 test 如下所示:

+-------+-------+
| u1_id | u2_id |
+-------+-------+
|     1 |     2 |
|     3 |     1 |
|     2 |     1 |
|     2 |     3 |
+-------+-------+

并且，u1_id 和 u2_id 都是另一个表 user 的“外键”:

+----+-------+
| id | name  |
+----+-------+
|  1 | n_foo |
|  2 | n_bar |
|  3 | n_baz |
+----+-------+

不知道如何解释，但是:

• 在输入中，我有一个用户 id，可以在 u1_id 或 u2_id 中引用。
• 我想使用表 user 上的连接让关联用户访问它，如表 test 中定义的那样。

对于用户 id = 1，我应该得到:

n_bar
n_baz
n_bar

对于用户 id = 2，我应该得到:

n_foo
n_foo
n_baz

这可能是一个常见问题，但没有找到确切的方法来连接这两个表:

• u1_id 如果我输入的用户 id 在 u2_id 列中
• u2_id 否则

我试过类似的东西，但它似乎不起作用:

SELECT name 
FROM test 
   JOIN user 
   ON user.id = test.u1_id 
WHERE test.u1_id = @guid OR 
      test.u2_id = @guid AND 
      CASE 
         WHEN test.u2_id = @guid 
         THEN test.u2_id = test.u1_id 
      END;

有什么想法可以实现吗？或者可能有更好的方法来设计这些表格，我完全愿意接受任何建议。

Answer 1

如果我正确理解了你的问题，我相信你需要以下查询:

SELECT t2.`name` 
FROM `t2`
INNER JOIN (
    SELECT IF(@uid = 1, t1.u1_id, t1.u2_id) as `id`
    FROM `t1`
) as `t1`
WHERE t2.id = t1.id and t1.id != @uid;

我试过以下:

日期库创建，我不知道你使用的是什么列类型，只是为了演示:

create table t1 (
    u1_id int,
    u2_id int
);
insert into t1 values 
(1,    2),
(3,    1),
(2,    1),
(2,    3);

create table t2 (
    id int,
    name varchar(10)
);
insert into t2 values 
(  1 , 'n_foo' ),
(  2 , 'n_bar' ),
(  3 , 'n_baz' );

然后查询:

mysql> SELECT * FROM t1;
+-------+-------+
| u1_id | u2_id |
+-------+-------+
|     1 |     2 |
|     3 |     1 |
|     2 |     1 |
|     2 |     3 |
+-------+-------+
4 rows in set (0.00 sec)

mysql> SELECT * FROM t2;
+------+-------+
| id   | name  |
+------+-------+
|    1 | n_foo |
|    2 | n_bar |
|    3 | n_baz |
+------+-------+
3 rows in set (0.00 sec)

mysql> SET @uid = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @uid;
+------+
| @uid |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> SELECT t2.`name` 
    -> FROM `t2`
    -> INNER JOIN (
    ->     SELECT IF(@uid = 1, t1.u1_id, t1.u2_id) as `id`
    ->     FROM `t1`
    -> ) as `t1`
    -> WHERE t2.id = t1.id and t1.id != @uid;
+-------+
| name  |
+-------+
| n_baz |
| n_bar |
| n_bar |
+-------+
3 rows in set (0.03 sec)

mysql> SET @uid = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @uid;
+------+
| @uid |
+------+
|    2 |
+------+
1 row in set (0.00 sec)

mysql> SELECT t2.`name` 
    -> FROM `t2`
    -> INNER JOIN (
    ->     SELECT IF(@uid = 1, t1.u1_id, t1.u2_id) as `id`
    ->     FROM `t1`
    -> ) as `t1`
    -> WHERE t2.id = t1.id and t1.id != @uid;
+-------+
| name  |
+-------+
| n_foo |
| n_foo |
| n_baz |
+-------+
3 rows in set (0.00 sec)

顺便说一句，如果不是您想要的，您可以更改加入条件。但是因为它给出了正确的结果......

试试吧!