SQL - 左连接多个条件 - 优先级

Question

我有两个表，结构类似如下：

表名：user
字段：id、active_office_address_id（可以为0）

表名：user_address
字段：id、user_id、type（家庭、办公室）

一个用户可以有一个“家庭”地址（非必需），以及多个“办公室”地址。我已经有了一个连接来获取用户地址，但是如果用户有一个“家庭”地址，我希望获取该地址，而不是“办公室”地址。

那么，如果存在“家庭”地址，我该如何获取它，只有当不存在时才获取“办公室”地址。（实际上，查询要复杂得多，连接涉及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

- morandi3

---

假设MySQL支持，使用coalesce函数将会对你有所帮助。 - Dan Bracuk

@morandi3 . . . 你需要所有的办公地址还是只需要一个？ - Gordon Linoff

Answer 1

您可以使用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

- Hart CO

---

可能需要使用 office.id？不是完全清楚，但重要的是COALESCE()和两次连接，因为地址数据很可能已经规范化了。 - Hart CO

家庭地址和办公室地址是什么？ID是什么？ - morandi3

它们将是您尝试返回的实际地址字段。 - Hart CO

Answer 2

使用两个左连接和一个条件语句可获取所需的地址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 = ?)

这里假设每个用户只有一个家庭地址。

- Tom McClure

---

是的，每个用户只有一个家庭地址。 - morandi3

Answer 3

至少在SQL Server中，不确定MySql是否也可以，在order by子句中可以使用case语句，例如：

order by user.id, case user_address.type when 'home' then 1 else 2 end, --additional ordering clauses here

- beercodebeer