mysql - 整理两个表中的数据

Question

我正在使用以下语句来尝试正确收集和显示数据。有必要对一个表进行“左连接”以收集更多信息，但我应该说对于第二种情况没有必要这样做(但这是我的解决方法)。

SELECT
    COALESCE(building.campus_id, campus.campus_id) AS campus 
    member.*
FROM location
LEFT JOIN cu_member AS member ON
    (member.member_id = location.member_id)
LEFT JOIN cu_building AS building ON
    (location.params LIKE 'building_id=%' = building.id) 
LEFT JOIN cu_campus AS campus ON
    (location.params LIKE 'campus_id=%' = campus.id)

我是上面的查询，我想使用通配符值。

LEFT JOIN cu_building AS building ON
    ('39' = building.id) 

下面是我的位置表的外观。我正在尝试使用 params 列中的数据从另一个表(building)获取生成的campus。我只需要对包含 building_id 标记的字段执行此操作，而不是对包含“campus_id”的字段执行此操作，因为这是已知的。

-----------------------------
 member_id  | params  
-----------------------------
 1          | building_id=39
 2          | building_id=24
 3          | campus_id=6
 4          | campus_id=3
 5          | building_id=11
 6          | campus_id=14
 7          | building_id=15

这就是我的building表的样子。它列出了哪个建筑物属于哪个校园的一部分。

--------------------------
 building_id  | campus_id 
--------------------------
 39           | 5
 24           | 4
 11           | 2
 15           | 2

我有另一个名为“campus”的表。我的问题是，这个表只是列出，但我希望使用这个表以便在最终结果中显示正确的数据。

--------------------------
 campus_id   | name 
--------------------------
 6           | ...
 3           | ..
 14          | .

我想用 MySQL 查询实现的结果是这样的。此处，收集的结果显示在一张表中。

-----------------------------
 member_id  | campus  
-----------------------------
 1          | 5                      (building_id=39)
 2          | 4                      (building_id=24)
 3          | 6                      (campus_id=6)
 4          | 3                      (campus_id=3)
 5          | 2                      (building_id=11)
 6          | 14                     (campus_id=14)
 7          | 2                      (building_id=15)

Answer 1

首先要做的事情。 您确实需要修改数据库结构。该位置表会给您带来无数的问题，使您将成员与建筑物和校园连接起来所需的每一个查询都变得复杂。根据显示的数据，您确实应该在成员表中包含building_id和campus_id。另一种更温和的解决方案是在位置表中添加building_id 和campus_id 列。

也就是说，您不需要正则表达式，也不需要 LIKE 来让这个查询工作。像这样的东西应该足够工作:

SELECT
  COALESCE(b.campus_id, c.id) AS campus 
  m.*
FROM      cu_member   m
JOIN      location    l ON l.member_id = m.member_id
LEFT JOIN cu_building b ON l.params = CONCAT('building_id=',b.id)
LEFT JOIN cu_campus   c ON l.params = CONCAT('campus_id=',c.id)

我可以看到最后一个加入似乎有点多余，因为您实际上只需要校园 ID，而不需要姓名或其他信息。它已经驻留在位置表中，因此似乎没有必要加入校园表。问题是它嵌入在 params 列中。提取它是一团糟。