基于 2 个表和一个连接表和行的 MySQL 查询应按顺序包含连接数据的所有实例

Question

我需要创建一个包含来自 2 个表的数据的表结果（并查明在 MySQL 的限制内是否可能），但查询本身将基于 3 个表（包括一个连接表）。并且连接的结果应该添加到输出结果中。

表结构：

registrants
id, first name, last name, industryID

industries
id, name

registrants_industries (join table)
id, registrant_id, industry_id

因此，在最终输出结果表中，任何注册者可以有0个、1个或多个行业，并且这些行业名称应添加到最终结果表的末尾。

registrants.id, 
registrants.first_name, 
registrants.last_name, 
industry.name-1,      
industry.name-2,  
industry.name-3,  
industry.name-4,  
industry.name-5.

但是所有列都应按行业对齐。例如，如果有 5 个行业，则输出将如下所示：（

行业按以下顺序排列： 绘画、瓷砖、一般建筑、庭院护理、屋顶）

354, Mike, Smith, Painting,’’,’’,Yard Care,’’
599, Joe, Jones, ‘’,’’,’General Construction’,’’,’’

因此所有行业列都会对齐并可以在 Excel 等中排序。

此查询输出是否可以使用 (My)SQL 进行？ 如果可以，您可以对此最好的方法有什么好的建议吗？

我开始编写查询，却发现没有答案可以根据匹配项解决我添加的列。并根据我的需要将所有内容按行组合在一起。

Answer 1

您可以使用 group_concat 并将所有行业放在一列中。我知道这不是您问题的准确答案，但它很接近，而且我不相信您所要求的仅用 3 个连接就可能实现。

SELECT
R.id, 
R.first_name, 
R.last_name, 
GROUP_CONCAT(I.name) AS industries
FROM registrants R
JOIN registrants_industries RI ON R.id = RI.registrant_id
JOIN industires I ON RI.industry_id = I.id
GROUP BY R.id

这将产生

354, Mike, Smith, "Painting,Yard Care"
599, Joe, Jones, "’General Construction’"

Answer 2

您可以使用子查询为每个 registrants_industries 条目分配一个编号：

select  r.id, 
,       r.first_name
,       r.last_name
,       min(case when ri.RowNr = 1 then i.name end) as [Industry-1]
,       min(case when ri.RowNr = 2 then i.name end) as [Industry-2]
,       min(case when ri.RowNr = 3 then i.name end) as [Industry-3]
,       min(case when ri.RowNr = 4 then i.name end) as [Industry-4]
,       min(case when ri.RowNr = 5 then i.name end) as [Industry-5]
from    registrants r
join    (
        select  (
                select  count(*)
                from    registrants_industries ri3
                where   ri3.registrant = r2.registrant
                        and ri3.id <= ri2.id
                ) as RowNr
        ,       *
        from    registrants_industries ri2
        ) ri
on      ri.registrant_id = r.id
join    industries i
on      i.id = ri.industry_id
group by
        r.id
,       r.first_name
,       r.last_name

Answer 3

您要求将值透视到相应的列中。这是一个常见的要求，特别是对于报告和导出到电子表格而言。

在 SQL 中，您需要在查询中声明列，并且它们在查询准备时固定，然后查询才有机会查看数据。您无法让查询根据找到的数据值自动将列动态添加到其结果集中。

解决方法是对要包含在查询结果中的每个不同值进行硬编码表达式：

SELECT r.id, r.first_name, r.last_name,
  MAX(CASE i.name WHEN 'Painting' THEN i.name END) AS `Painting`,
  MAX(CASE i.name WHEN 'Tiling' THEN i.name END) AS `Tiling`,
  MAX(CASE i.name WHEN 'General Construction' THEN i.name END) AS `General Construction`,
  MAX(CASE i.name WHEN 'Yard Care' THEN i.name END) AS `Yard Care`,
  MAX(CASE i.name WHEN 'Roofing' THEN i.name END) AS `Roofing`
FROM registrants r
LEFT OUTER JOIN (
  registrants_industries ri
  INNER JOIN industries i ON i.id = ri.industry_id
) ON r.id = ri.registrant_id
GROUP BY r.id;

这意味着您要么需要知道数据中可能存在哪些不同值，要么需要发现它在运行上述查询之前：

SELECT DISTINCT name FROM industries;

然后编写应用程序代码以使用该结果生成一个具有可变列数的 SQL 查询字符串，一个列表达式对应于每个不同的行业名称。

另一种方法是不使用 SQL 进行透视，而是将原始数据提取回应用程序，并根据用户 ID 提取多行，将结果排序到应用程序数据结构中，直到获得一些看起来像您想要的透视数据集的网格视图。

无论哪种方式，如果您希望查询能够适应数据并为存在的每个数据值返回一列，则需要编写应用程序代码。您可以编写代码来预处理数据并生成 SQL 查询，或者编写代码来在获取数据时对数据进行后处理。