MySQL:使用CASE/ELSE值作为连接参数 [英] MySQL: Use CASE/ELSE value as join parameter

问题描述

我试图根据谁是付款人或收款人，将"USERS"表中的"NAME"和"PHOTO"加入到"TRANSACTIONS"表中.它总是告诉我找不到表this-我在做什么错了?

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?

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

推荐答案

来自

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

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

您不能在联接中使用别名.您只能在上面列出的地方使用它.原因是别名在联接结果中的字段上.如果允许在其定义中加入这些别名，则将(或可能)导致递归定义.

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.

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

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

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:

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

结果:

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

测试数据:

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);

这篇关于MySQL:使用CASE/ELSE值作为连接参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！