mysql - MySQL JOIN 查询 - 左表的每一行从右表中提取一行，并优先考虑包含的数据

Question

我有两张桌子，房子和租客。在每个单位有多个租户。我需要的是一个加入列表，最多包含两个租户，具有完整数据集的优先行，然后是电话号码和最后一个电子邮件地址。

我还想避免临时表和子查询，因为有大量数据。谢谢！

例子：

餐桌屋

-------------
| id | flat|
-------------
| 1  | 011 |
| 2  | 012 |
| 3  | 111 |
-------------

餐桌租用者

------------------------------
| fid | name | phone | mail   |
------------------------------
| 1   | pete | NULL  | NULL   |
| 1   | cloe | NULL  | cloe@..|
| 1   | bill | 555.. | bill@..|
| 2   | john | 555.. | john@..|
| 3   | paul | 555.. | NULL   |
| 3   | mary | NULL  | mary@..|
------------------------------

预期输出：

-----------------------------------------------------------------
| id | flat | name1 | phone1 | mail1  | name2 | phone2 | mail2  |
-------------
| 1  | 011  | bill  | 555..  | bill@..| cloe  | NULL   | cloe@..|
| 2  | 012  | john  | 555..  | john@..| NULL  | NULL   | NULL   |
| 3  | 111  | paul  | 555..  | NULL   | mary  | NULL   | mary@..|
-----------------------------------------------------------------

标签： mysqlsqljoin

Answer 1

在 MySQL8 中：

SELECT 
  f.id,
  f.flat,
  MAX(CASE WHEN rr.rn = 1 THEN rr.`name` END) AS name1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.phone END) AS phone1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.mail END) AS email1,
  MAX(CASE WHEN rr.rn = 2 THEN rr.`name` END) AS name2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.phone END) AS phone2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.mail END) AS email2
FROM
house f
LEFT JOIN
(
 SELECT 
  r.*, 
  ROW_NUMBER() OVER(PARTITION BY r.fid ORDER BY 
    (CASE WHEN r.phone IS NOT NULL THEN -2 ELSE 0 END + CASE WHEN r.mail IS NOT NULL THEN -1 ELSE 0 END), r.fid
  ) rn
 FROM
  renter r
) rr
ON rr.fid = f.id and rr.rn <= 2
GROUP BY f.id, f.flat

在 MySQL < 8 中，您必须使用这种未记录的（可能会突然停止工作）技术来伪造 ROW_NUMBER/PARTITION：

SELECT 
  f.id,
  f.flat,
  MAX(CASE WHEN rr.rn = 1 THEN rr.`name` END) AS name1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.phone END) AS phone1,
  MAX(CASE WHEN rr.rn = 1 THEN rr.mail END) AS email1,
  MAX(CASE WHEN rr.rn = 2 THEN rr.`name` END) AS name2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.phone END) AS phone2,
  MAX(CASE WHEN rr.rn = 2 THEN rr.mail END) AS email2
FROM
house f
LEFT JOIN
(
 SELECT 
  r.*, 
  @rn:=CASE WHEN r.fid=@previd THEN @rn+1 ELSE 1 END as rn,
  @previd:=r.fid
 FROM
  (select @rn:=0,@previd:=-1) x,
  renter r
  ORDER BY r.fid, (CASE WHEN r.phone IS NOT NULL THEN -2 ELSE 0 END + CASE WHEN r.mail IS NOT NULL THEN -1 ELSE 0 END)
) rr
ON rr.fid = f.id and rr.rn <= 2
GROUP BY f.id, f.flat

https://www.db-fiddle.com/f/dYS68AFFGTxZxfia1UtJEK/0

这个怎么运作：

您的租户表应用了一个行号，它按优先级顺序计算行数。如果一行有电话，则得分-2，如果一行有电子邮件，则得分-1。如果一行两者都有，则将它们加在一起为-3。当按升序排序时，这意味着 -3 比 -2 或 -1 具有更高的优先级（是排序中的第一行）。行号在行上放置一个数字，如 1,2,3.. 每次平面 ID 号更改时它都会重新启动。

我们将我们的扩充数据集加入到平面中，abd 我们说我们只对行号上的行 <=2 感兴趣，因为您只需要 name1 和 name2 等。

但是这些数据仍然在一个列中：

FlatID, Name,  RN
1,      Bill,  1
1,      Cloe,  2

要将列转换为行，我们使用旋转操作。执行此操作的标准方法是使用CASE WHEN rn = 1 or 2 ...：

SELECT *, case when rn = 1 then name end as name, case when rn = 2 then name end

生产

FlatID, Name1,  Name2,  RN
1,      Bill,   null,   1
1,      null,   Cloe,   2

现在我们使用 MAX() 将 FlatID 组合成一行，并且因为 MAX 丢弃空值，所以 Bill 和 Cloe 被保留并成为一行。RN 完成了它的工作并被丢弃：

FlatID, Name1,  Name2
1,      Bill,   Cloe

底部查询（mysql5.x）使用​​相同的技术，它只是使用变量来模仿 row_number()

---

对于未来的问题，请确保您发布 MySQL 版本，并尝试在 db-fiddle.com 上制作一组示例数据（就像我在上面所做的那样）或类似的 - 如果他们不这样做，它将让更多人有兴趣帮助您必须搞乱创建表格并用数据加载它们来测试他们的理论