MySQL：使用CASE / ELSE值作为连接参数

I'm trying to join the NAME and PHOTO from USERS table to the TRANSACTIONS table based on who is the payer or payee. It keeps telling me can't find the table this -- What am I doing wrong?

我正在尝试根据谁是付款人或收款人，将USERS表中的NAME和PHOTO加入TRANSACTIONS表。它一直告诉我找不到这张桌子 - 我做错了什么？

SELECT `name`,`photo`,`amount`,`comment`,
(
    CASE `payer_id`
    WHEN 72823 THEN `payee_id`
    ELSE `payer_id`
    END
) AS `this`
FROM `transactions`
RIGHT JOIN `users` ON (`users`.`id`=`this`)
WHERE `payee_id`=72823 OR `payer_id`=72823

1 个解决方案

#1

From the documentation about aliases:

从有关别名的文档：

The alias is used as the expression's column name and can be used in GROUP BY, ORDER BY, or HAVING clauses.

别名用作表达式的列名，可以在GROUP BY，ORDER BY或HAVING子句中使用。

You can't use an alias in a join. You can use it only in the places listed above. The reason is that the alias is on a field in the result of the join. If the join were allowed to these aliases in its definition it would (or could) result in recursive definitions.

您不能在联接中使用别名。您只能在上面列出的位置使用它。原因是别名位于连接结果中的字段上。如果允许连接在其定义中使用这些别名，它将（或可能）导致递归定义。

To solve your problem you could repeat the CASE clause in both places:

要解决您的问题，您可以在两个地方重复CASE子句：

SELECT `name`,`photo`,`amount`,`comment`,
(
    CASE `payer_id`
    WHEN 72823 THEN `payee_id`
    ELSE `payer_id`
    END
) AS `this`
FROM `transactions`
RIGHT JOIN `users` ON `users`.`id`= (
    CASE `payer_id`
    WHEN 72823 THEN `payee_id`
    ELSE `payer_id`
    END
)
WHERE `payee_id`=72823 OR `payer_id`=72823

However I would probably rewrite this query as two selects and UNION them:

但是，我可能会将此查询重写为两个选择和UNION它们：

SELECT name, photo, amount, comment, payer_id AS this
FROM transactions
JOIN users ON users.id = payer_id
WHERE payee_id = 72823
UNION ALL
SELECT name, photo, amount, comment, payee_id AS this
FROM transactions
JOIN users ON users.id = payee_id
WHERE payer_id = 72823

Result:

结果：

'name3', 'photo3', 30, 'comment3', 3
'name1', 'photo1', 10, 'comment1', 1
'name2', 'photo2', 20, 'comment2', 2

Test data:

测试数据：

CREATE TABLE users (id INT NOT NULL, name NVARCHAR(100) NOT NULL, photo NVARCHAR(100) NOT NULL);
INSERT INTO users (id, name, photo) VALUES
(1, 'name1', 'photo1'),
(2, 'name2', 'photo2'),
(3, 'name3', 'photo3'),
(4, 'name4', 'photo4');

CREATE TABLE transactions (amount INT NOT NULL, comment NVARCHAR(100) NOT NULL, payer_id INT NOT NULL, payee_id INT NOT NULL);
INSERT INTO transactions (amount, comment, payer_id, payee_id) VALUES
(10, 'comment1', 72823, 1),
(20, 'comment2', 72823, 2),
(30, 'comment3', 3, 72823),
(40, 'comment4', 4, 5);

#1