php MySQL：使用 CASE/ELSE 值作为连接参数
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2761574/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: Use CASE/ELSE value as join parameter
提问by DRJ
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?
我试图根据谁是付款人或收款人将 NAME 和 PHOTO 从 USERS 表加入到 TRANSACTIONS 表。它一直告诉我找不到桌子this——我做错了什么？
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
回答by Mark Byers
From the documentationabout 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 CASEclause 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:
但是，我可能会将这个查询重写为两个选择并联合它们：
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);
