MySQL查询基于2个表和一个连接表和行应该包括所有的实例，按顺序，连接数据 - MySQL query based on 2 tables and a join table and rows should include all instances, in order, of joined data

我需要创建一个表结果(并找出是否可能在MySQL的约束内)，将包含来自2个表的数据，但查询本身将基于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中排序等。

这个查询输出与(我的)SQL可能吗?如果是的话，你能就最好的方法提出什么好的建议吗?

我开始写我的查询，只是发现没有答案来解决我添加的基于匹配的列。然后按行排列

您可以使用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’"

您可以使用子查询为每个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

您要求透视值到各自的列中。这是一个常见的需求，特别是在报告和导出电子表格时。

在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查询，或者编写代码来在获取数据时对数据进行后处理。