mysql - 如何在 SQL 查询中显示多列中的所有值

Question

我正在尝试显示来自数据库中不同表的一些信息。其中一个表称为 job_pieces。在此表中有字段 ID、CompanyID 和 PieceType。每个公司可以有很多 ID 和 PieceType。我不会知道每个公司有多少个 PieceType。这只是一个小例子，但有些公司有将近 30 种 PieceType。这是一个示例数据库: http://www.sqlfiddle.com/#!9/6d528/2

SELECT  c.Name,
        COUNT(distinct jn.ID) as Jobs
        GROUP_CONCAT(DISTINCT jp.PieceType) as AllPieceTypes,
        jp.PieceType as PieceType        
FROM customer c
LEFT JOIN job_new jn ON c.JobID = jn.ID
LEFT JOIN job_pieces jp ON jn.JobID = jp.JobID
WHERE c.Company_ID = compid
GROUP BY c.ID

这个查询的结果是这样的:

我添加了 GROUP_CONCAT 行，这样您就可以看到作业具有的所有 PieceType。因此，在此查询中，我需要显示公司拥有的所有职位和 PieceType。问题出在列 PieceType 上。正如您在第一行的图像中看到的那样，它只显示一种类型，而实际上应该有 4 种。而且我还希望每个 PieceType 都是一个单独的列，如下所示:

根据我所做的搜索，将 AllPieceTypes 列拆分为每个片段的单独列似乎是一件困难的事情。那么我是否加入了 job_pieces 表错误？为什么 PieceType 栏中显示的是一种类型，而存在多种类型？以及如何在其自己的列中显示每个 PieceType？他们需要在自己的列中，因为每个公司只有一行。所以我需要在显示 PieceTypes 时继续该行

我在这里找到了类似的东西:How to parse a string and create several columns from it?但我不确定如何为我的查询编辑它。

Answer 1

您可以采用不同的方法。但真正的问题是你需要预先确定有多少这样的类型。 SQL 查询返回一组固定的行，这就是 group_concat() 很方便的原因。

下面是一个返回前三种片段类型的方法的示例:

SELECT  c.Name,
        COUNT(distinct jn.ID) as Jobs
        GROUP_CONCAT(DISTINCT jp.PieceType) as AllPieceTypes,
        substring_index(GROUP_CONCAT(DISTINCT jp.PieceType ORDER BY jp.PiectType), ',', 1) as PieceType1,
        substring_index(substring_index(GROUP_CONCAT(DISTINCT jp.PieceType ORDER BY jp.PiectType), ',', 2), ',', -1) as PieceType2,
        substring_index(substring_index(GROUP_CONCAT(DISTINCT jp.PieceType ORDER BY jp.PiectType), ',', 3), ',', -1) as PieceType3
FROM customer c LEFT JOIN
     job_new jn
     ON c.JobID = jn.ID LEFT JOIN
     job_pieces jp
     ON jn.JobID = jp.JobID
WHERE c.Company_ID = compid
GROUP BY c.ID

(如果某些作业少于 3 个，那么逻辑会稍微复杂一点，因为这会重复值。)