MySQL SQL - LEFT JOIN 多个条件 - 优先级
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20357689/
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
SQL - LEFT JOIN multiple conditions - priority
提问by morandi3
I have 2 tables with a structure similar with this:
我有 2 个表，其结构与此类似：
table: user
fields: id, active_office_address_id (this can be 0)
table: user_address
fields: id, user_id, type (home, office)
表：用户
字段：id、active_office_address_id（可以为 0）
表：user_address
字段：id、user_id、类型（家庭、办公室）
A user can have a "home" address (not mandatory) and multiple "office" addresses. I have a join to get a user address, but I want that if the user have a "home" address to get that address, not "office" address.
一个用户可以有一个“家庭”地址（非强制性）和多个“办公”地址。我有一个加入来获取用户地址，但我希望如果用户有一个“家庭”地址来获取该地址，而不是“办公室”地址。
So, how can I get "home" address if exists, and only if that not exists to get "office" address. (In reality the query is much more complicated and the join is done on 4-5 tables)
那么，如果存在，我怎样才能获得“家庭”地址，并且只有在不存在的情况下才能获得“办公室”地址。（实际上查询要复杂得多，连接是在 4-5 个表上完成的）
SELECT * FROM user LEFT JOIN user_address ON (user.id = address.user_id AND
(user_address.type = "home" OR user.active_office_address_id = user_address.id))
group by user.id
回答by Hart CO
You can use COALESCE()and join to your address table twice:
您可以使用COALESCE()并加入您的地址表两次：
SELECT user.id
,COALESCE(home.address, office.address) AS Address
FROM user
LEFT JOIN user_address AS home
ON user.id = home.user_id
AND home.type = "home"
LEFT JOIN user_address AS office
ON user.active_office_address_id = office.user_id
GROUP BY user.id
回答by Tom McClure
Two left joins and a case statement will give you the address id you want.
两个左连接和一个 case 语句会给你你想要的地址 ID。
SELECT user.*,CASE WHEN home_addr.id IS NOT NULL THEN home_addr.id ELSE ofc_addr.id END AS addr_id
FROM user
LEFT JOIN user_address AS home_addr
ON (user.id = home_addr.user_id AND home_addr.type = 'home')
LEFT JOIN user_address AS ofc_addr
ON (user.active_office_address_id = ofc_addr.id)
You could feed this back in as a sub-select for a particular user:
您可以将此作为特定用户的子选择反馈：
SELECT * FROM user LEFT JOIN user_address
WHERE user.id = ?
AND user_address.user_id = user.id
AND user_address.id IN
(SELECT CASE WHEN home_addr.id IS NOT NULL THEN home_addr.id ELSE ofc_addr.id END AS addr_id
FROM user
LEFT JOIN user_address AS home_addr
ON (user.id = home_addr.user_id AND home_addr.type = 'home')
LEFT JOIN user_address AS ofc_addr
ON (user.active_office_address_id = ofc_addr.id)
WHERE user.id = ?)
This assumes that only one home address exists per user.
这假设每个用户只存在一个家庭地址。
回答by beercodebeer
At least in SQL Server, not sure about MySql, you can use a casestatement in the order by clause, for example:
至少在SQL Server中，对MySql不确定，可以case在order by子句中使用语句，例如：
order by user.id, case user_address.type when 'home' then 1 else 2 end, --additional ordering clauses here
