MySQL JOIN 查询 - 右表中的一行对应左表中的每一行，并优先考虑所包含的数据

Question

我有两张 table ，House 和 Renter。每个公寓都有多个租户。我需要的是一个连接列表，最多包含两个租户、具有完整数据集的优先行，然后是电话号码和最后的电子邮件地址。

我还想避免使用临时表和子查询，因为存在大量数据。谢谢!

示例:

table 屋

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

餐 table 承租人

------------------------------
| 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@..|
-----------------------------------------------------------------

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()

<小时/>

对于 future 的问题，请确保您发布了 MySQL 版本，并尝试在 db-fiddle.com 上创建一组示例数据(就像我上面所做的那样)或类似的 - 这会让更多的人有兴趣帮助您，如果他们不必费力创建表格并加载数据来测试他们的理论