SQL -左联接多个条件-优先级
SQL -左联接多个条件-优先级
提问于 2013-12-03 17:24:27
我有两张表，其结构与此类似：
表:用户 字段: id，active_office_address_id (可以是0) 表: user_address 字段: id、user_id、type (home、office)
用户可以有"home“地址(不是强制性的)和多个"office”地址。我有一个连接来获得一个用户地址，但是我希望如果用户有一个"home“地址来获得那个地址，而不是"office”地址。
所以，如果有，我怎么能得到“家”地址，只有当那个地址不存在的时候，才能得到“办公室”地址。(实际上，查询要复杂得多，连接是在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回答 3
Stack Overflow用户
发布于 2013-12-03 17:36:26
您可以使用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.idStack Overflow用户
发布于 2013-12-03 17:35:22
两个左联接和一个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)您可以将其作为特定用户的子选择提供：
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 = ?)这假设每个用户只存在一个家庭地址。
Stack Overflow用户
发布于 2013-12-03 17:30:17
至少在Server中，对于MySql不确定，您可以在order子句中使用case语句，例如：
order by user.id, case user_address.type when 'home' then 1 else 2 end, --additional ordering clauses here页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/20357689
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号