将多行合并成一个MySQL Join [英] Combine multiple rows into one MySQL Join

问题描述

我想不出一个更好的方法来问这个问题，但是这里：

我有两个表。第一个是用于标签打印作业的项目表。我们将它称为 pj_items 列只是： job_id，part_num和qty

第二个表是一个名为 PartFinishes 的部分列表，只有两列： PartNumber和FinishId

当我要检索标签打印机的信息时，我使用：

select pj_items.part_num， pj_items.qty，PartFinishes.FinishId from pj_items

在PartFinishes.PartNumber = pj_items.part_num上连接PartFinishes

where job_id = 1

此查询运行正常，一个完成。问题是当零件上有两个或更多的饰面。

例如：零件 99401326 有2个表面。在我的 pj_items 表中，我有一行的那个部分，所以我可以打印一个标签。当我使用这个查询时，它返回两行，每个都有不同的 FinishId 不幸的是，将导致2个标签打印。我需要在一行上的这两个完成，因为我只想要一个标签打印的 FinishId 的存在。

我有一个类似的查询，返回的结果作为新的列（ f1，f2，f3 ）但它仍然返回2行在给定的例子中 f1 在第一行定义， f1 在第二行定义为null， f2

希望这是有意义的，任何人都有任何建议吗？

如果您需要更多信息，请告诉我。

EDIT：

pj_items 表可能有重复的条目，我需要将它们保存为单独的行。

示例

在此处是pj_items表的示例：

这是来自 PartFinishes 表的相关信息：

这里是使用原始查询的结果：

我需要的结果是两行 99401326 是同一行中的两个完成，同时保持 99401077 的两个独立行

解决方案

您可以使用GROUP_CONCAT将给定零件的所有完成ID合并为一个列。 p>

  SELECT pj_items.part_num，
 pj_items.qty，
 GROUP_CONCAT（PartFinishes.FinishId）as FinishIds 
 FROM pj_items 
 JOIN PartFinishes 
 ON PartFinishes.PartNumber = pj_items.part_num 
 WHERE job_id = 1 
 GROUP BY PartFinishes.PartNumber 
  

@comment：
然而，如果你想保持pj_items的行之间的分离，你将需要加入已经按PartNumber分组的PartFinishes：

  SELECT pj_items.part_num，
 pj_items.qty，
 FinishesGrouped.FinishIds 
 FROM pj_items 
 JOIN 
（SELECT PartNumber，GROUP_CONCAT（FinishId）as FinishIds 
 FROM PartFinishes 
 GROUP BY PartNumber）FinishesGrouped 
 ON 
 pj_items.part_num = FinishesGrouped.PartNumber 
  

I couldn't think of a better way to ask this question, but here goes:

I have 2 tables. The first is a table of items for label printing jobs. We'll call it pj_items The columns are simply: job_id, part_num and qty

The second table is a list of finishes called PartFinishes on parts with just 2 columns of: PartNumber and FinishId

When I want to retrieve information for the label printer I use:
select pj_items.part_num, pj_items.qty, PartFinishes.FinishId from pj_items
join PartFinishes on PartFinishes.PartNumber = pj_items.part_num
where job_id = 1

This query works fine and gives me the right result when each part has only one finish. The problem is when there are 2 or more finishes on a part.

For example: The part 99401326 has 2 finishes. On my pj_items table I have one row for that part so I can print one label. When I use this query it returns 2 rows for that part each with a different FinishId Unfortunately that would result in 2 labels printed. I need both of those finishes on one row since I only want one label printed with both FinishId's present.

I had a similar query which returned the finishes as new columns (f1,f2,f3) But it would still return 2 rows in the given example with f1 defined in the first row and f1 as null in the second row and f2 being defined in the second.

How can I get that into one row?

Hopefully that makes sense, anyone have any suggestions?

If you need anymore info, let me know.

Thanks!

EDIT:

The pj_items table may have duplicate entries and I need to preserve those as separate rows. I just need to have all the finishes for each part in one row with its respective part.

Example
Here is an example of the pj_items table:

Here is the relevant information from the PartFinishes table:

And here is the result using the original query:

What I need in the result is the two rows of 99401326 are one with both of the finishes in that row, while maintaining the 2 seperate rows for the 99401077 since I want 2 labels of those.

解决方案

You can use GROUP_CONCAT to combine all finish ID's for a given part into one column.

SELECT pj_items.part_num, 
       pj_items.qty, 
       GROUP_CONCAT(PartFinishes.FinishId) as FinishIds
FROM pj_items
JOIN PartFinishes 
  ON PartFinishes.PartNumber = pj_items.part_num
WHERE job_id = 1
GROUP BY PartFinishes.PartNumber

@comment: However if you want to keep rows from pj_items separated you will need to join PartFinishes already grouped by PartNumber:

SELECT pj_items.part_num, 
   pj_items.qty, 
   FinishesGrouped.FinishIds
FROM pj_items
JOIN 
  ( SELECT PartNumber, GROUP_CONCAT(FinishId) as FinishIds
    FROM PartFinishes
    GROUP BY PartNumber ) FinishesGrouped
ON
  pj_items.part_num = FinishesGrouped.PartNumber

这篇关于将多行合并成一个MySQL Join的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！