mysql -连接三个表并在一个单元格中组合不同的项。

Question

我有三张不同的桌子：

business包含有关不同公司的信息。

business
-------------------------------------------------
  id     registry   city_id   primary_activity_id
-------------------------------------------------
  12     24212432         1                     1
 123     43143321         2                     3     
 432     53242123         3                   431

business_activity描述每个公司的次要活动。

business_activity
------------------------------------------------
business_secondary_activities_id     activity_id
------------------------------------------------
                              12             765
                             123             109
                             432               8
                              12             431
                              12               1
                             432               3

activity

activity
-----------------------------------
  id     identifier     description
-----------------------------------
   1           24-2    construction
   3           35-1     consultancy
 431           12-3   manufacturing
 765           23-2     electronics
 109           34-2        software 
   8            1-2          retail

它们以下列方式相互联系：

• business_activity.business_secondary_activities_id = business.id
• business.primary_activity_id = activity.id
• business_activity.activity_id = activity.id

我只想构建一个如下所示的表：

business
----------------------------------------------------------------------------------------------------------------------------------------
  id     registry   city_id   primary_activity_id  primary_activity_identifier secondary_activities_ids  secondary_activities_identifier
----------------------------------------------------------------------------------------------------------------------------------------
  12     24212432         1                     1                        24-2                765,431,1                  23-2, 12-3, 24-2
 123     43143321         2                     3                        35-5                      109                              34-2
 432     53242123         3                   431                        12-3                      8,3                         1-2, 35-1
----------------------------------------------------------------------------------------------------------------------------------------

请注意，secondary_activities_id和secondary_activity_identifier在一个单元格中组合在一起，用逗号分隔。

有人能帮忙吗？

Answer 1

SELECT b.id, 
       b.registry, 
       b.city_id, 
       b.primary_activity_id, 
       a1.identifier AS primary_activity_identifier,
       GROUP_CONCAT(DISTINCT ba.activity_id ORDER BY ba.activity_id ASC SEPARATOR ', ') 
           AS secondary_activities_ids,
       GROUP_CONCAT(DISTINCT a2.identifier ORDER BY a2.identifier ASC SEPARATOR ', ')
           AS secondary_activities_identifier
FROM business b
     LEFT JOIN activity a1 ON a1.id = b.primary_activity_id
     LEFT JOIN business_activity ba ON ba.business_secondary_activities_id = b.id
     LEFT JOIN activity a2 ON a2.id = ba.activity_id
GROUP BY b.id, b.registry, b.city_id, b.primary_activity_id, a1.identifier
ORDER BY b.id

FROM子句是相当直接的。您的基础是业务ID。每个业务都有一个主要业务活动，并且可能有多个(或无)辅助活动。因此，左联接关联所有辅助活动，活动表被连接两次，以解码这些数字对应的内容。

这个问题的关键是使用GROUP_CONCAT()函数。它是一个聚合函数(类似于计数或和)，用于带有GROUP BY子句的查询中。它获取组列中的所有记录，并将它们放入单个记录中。在这种情况下，您没有指定希望如何排序，所以我选择了升序。记住，良好的设计实践表明，这两个字段至少应该按照相同的顺序排序，以防止用户对他或她所看到的内容感到困惑。