如何在 MySQL 连接查询中使用 IF 语句？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10064183/
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
How do I use an IF statement in an MySQL join query?
提问by John Hoffman
I have a SQL query that left joins a table in different ways depending on a condition.
我有一个 SQL 查询，它根据条件以不同的方式连接一个表。
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
IF (u.id=m.user2ID)
LEFT JOIN users AS u ON u.id = m.user1ID
ELSE
LEFT JOIN users AS u ON u.id = m.user2ID
ENDIF
WHERE m.user1ID=2 OR m.user2ID=2
matchesis a table with integer columns user1ID and user2ID. usersis a table containing users of my web application. usershas a VARCHAR field called first_name.
matches是一个包含整数列 user1ID 和 user2ID 的表。users是一个包含我的 Web 应用程序用户的表。users有一个名为 的 VARCHAR 字段first_name。
The intention of this query is to get the names of the users matched with the current user.
此查询的目的是获取与当前用户匹配的用户名称。
However, MySQL returns this error:
但是，MySQL 返回此错误：
#1064 - You have an error in your SQL syntax;
check the manual that corresponds to your MySQL server version for
the right syntax to use near 'IF (u.id=m.user2ID) LEFT JOIN users
AS u ON u.id = m.user1ID ELSE LEFT JOIN user' at line 3
Why?
为什么？
回答by Dojo
Specify the condition as part of the ON clause:
将条件指定为 ON 子句的一部分：
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON (u.id=m.user2ID and u.id = m.user1ID) or (u.id<>m.user2ID and u.id = m.user2ID)
WHERE m.user1ID=2 OR m.user2ID=2
Another way to do the same thing:
做同样事情的另一种方法：
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON IF(u.id=m.user2ID,u.id = m.user1ID,u.id = m.user2ID)
WHERE m.user1ID=2 OR m.user2ID=2
回答by Somnath Muluk
Use this query:
使用此查询：
SELECT m.id, u.first_name AS otherUser FROM matches AS m LEFT JOIN users AS u ON u.id = m.user1ID AND u.id=m.user2ID LEFT JOIN users AS u1 ON u1.id = m.user2ID WHERE m.user1ID=2 OR m.user2ID=2
SELECT m.id, u.first_name AS otherUser FROM 匹配 AS m LEFT JOIN users AS u ON u.id = m.user1ID AND u.id=m.user2ID LEFT JOIN users AS u1 ON u1.id = m.user2ID WHERE m .user1ID=2 或 m.user2ID=2
Updated query:
更新的查询：
SELECT m.id, u.first_name AS otherUser
FROM matches AS m
LEFT JOIN users AS u ON u.id = (CASE WHEN u.id=m.user2ID
THEN m.user1ID
ELSE m.user2ID
END)
WHERE m.user1ID=2 OR m.user2ID=2
Check this Source.
检查这个来源。
Also check this MYSQL Inner Join if statement. It might be helpful for you.
还要检查这个MYSQL Inner Join if 语句。它可能对你有帮助。
回答by Alfred Godoy
You can not use the IF THEN ELSE END IF-stuff in a SELECTin this way. However, you can use it in stored procedures and functions.
您不能以这种方式IF THEN ELSE END IF在 aSELECT中使用-stuff 。但是，您可以在存储过程和函数中使用它。
I would JOINu.idwith both m.user1IDand m.user2IDand use DISTINCTto avoid duplicates.
我会JOINu.id同时使用m.user1IDandm.user2ID和 useDISTINCT来避免重复。
There is a IF()which you can use in SELECTs, but you can not do flow control with IF().
有一个IF()可以在 SELECT 中使用，但不能使用IF().
回答by Atul Mittal
Thanks, It worked for me. Tried something else like below :
谢谢，它对我有用。尝试其他类似下面的方法：
**
**
Create table TEMP1 (ID INT,T_MONTH INT,T_YEAR INT)
INSERT INTO TEMP1 VALUES(1,1,2001)
INSERT INTO TEMP1 VALUES(2,2,2001)
INSERT INTO TEMP1 VALUES(3,3,2001)
**
**
CREATE TABLE TEMP2 (T_MONTH INT,T_YEAR INT,FREQUENCY CHAR(1),VAL FLOAT)
INSERT INTO TEMP2 VALUES(1,2001,'M',1.1)
INSERT INTO TEMP2 VALUES(3,2001,'M',1.2)
INSERT INTO TEMP2 VALUES(3,2001,'Q',1.3)
INSERT INTO TEMP2 VALUES(12,2001,'A',1.4)
SELECT * FROM TEMP1 L JOIN TEMP2 H
ON L.T_YEAR = H.T_YEAR
OR (L.T_MONTH = (CASE WHEN H.FREQUENCY='M'
THEN H.T_MONTH
END)
OR dbo.GetQuarterFromMonth(L.T_MONTH) = (CASE WHEN H.FREQUENCY = 'Q'
THEN dbo.GetQuarterFromMonth(H.T_MONTH)
END))
WHERE H.FREQUENCY = 'Q'
Here GetQuarterFromMonth is a user defined funtion which returns Quarter for a particular month.
这里 GetQuarterFromMonth 是一个用户定义的函数，它返回特定月份的季度。
The objective of above query is to inflate the value of Quarter for all the months in table 1. If frequency is annual then in that case the value should be inflated for all the months 1-12 of table 1 which is achieved through first join condition. In case frequency is Monthly then each month from table1 should be mapped to table2. The only unique case is handling quarter frequency.
上述查询的目的是膨胀表 1 中所有月份的 Quarter 值。如果频率是年度，那么在这种情况下，表 1 的所有月份 1-12 的值都应该膨胀，这是通过第一个连接条件实现的. 如果频率是 Monthly，那么 table1 中的每个月都应该映射到 table2。唯一的独特情况是处理四分之一频率。
回答by sdexp
You can also have two LEFT JOINon the same table like this...
你也可以LEFT JOIN像这样在同一张桌子上放两个...
LEFT JOIN users AS u ON u.id = m.user1ID
LEFT JOIN users AS u2 ON u2.id = m.user2ID
This is an alternative to using IF statements.
这是使用 IF 语句的替代方法。
回答by Er Bhaskar Dutt Sharma
The image will help you to find the structure of db..$userIdis Uses id of user we wish to see the friend list for.
该图像将帮助您找到 db 的结构.. $userIdis 使用我们希望查看其好友列表的用户的 id。
SELECT `connections`.*,`usr`.*,"'.$existImage.'" as main_image_url,"'.$existThumb.'" as thumb_image_url FROM `connections` `connections` JOIN `users` `usr` ON `usr`.`userId` = (CASE WHEN `connections`.`requestBy`="'.$userId.'" THEN connections.requestFor ELSE connections.requestBy END) WHERE `connections`.`requestBy` = "'.$userId.'" OR `connections`.`requestFor` = "'.$userId.'" AND `connections`.`requestStatus` = 1
