如何将单独的记录合并为一个？

I have a table product and two columns id,pricing_category, and I have a query

我有一个表产品和两列id,pricing_category,我有一个查询

SELECT DISTINCT ppp.`id` AS productid ,ppp.`pricing_category` AS type1
FROM `product` ppp
WHERE ppp.`finish_product_id`=1

The result is:

结果是:

I want that type will appear as type1,type2,type3 in a single row for one id. How should I do it?

我希望该类型在一行中显示为type1,type2,type3。我该怎么办?

2 个解决方案

#1

You can do it using conditional aggregation:

您可以使用条件聚合来完成:

SELECT t.productID,
       MAX(CASE WHEN t.type1 = 1 THEN 1 ELSE 0 END) as type1_ind,
       MAX(CASE WHEN t.type1 = 2 THEN 1 ELSE 0 END) as type2_ind,
       MAX(CASE WHEN t.type1 = 3 THEN 1 ELSE 0 END) as type3_ind
FROM YourTable t
GROUP BY t.productID

This is dynamic for all productID, not just a specific one. To select a specific one, replace the group by with your WHERE clause.

这对于所有productID都是动态的,而不仅仅是特定的。要选择特定的一个,请将WHERE子句替换为group by。

This will return an indication for each product on every type if he has it(1) or not(0).

这将返回每种类型的每种产品的指示,如果他有(1)或不(0)。

So for example for the data:

例如,对于数据:

PRODUCTID | Type1
   1          1
   1          2
   1          3
   2          3

This will return:

这将返回:

PRODUCTID | TYPE1_IND | TYPE2_IND | TYPE3_IND
    1           1            1           1
    2           0            0           1

Or to get them all as one column use GROUP_CONCAT()

或者将它们全部作为一列使用GROUP_CONCAT()

SELECT t.productID,
       GROUP_CONCAT(concat('TYPE',t.type1))
FROM YourTable t
GROUP BY t.productID

#2

You can use GROUP_CONCAT :

您可以使用GROUP_CONCAT:

select id productid,
       group_concat(concat('type', pricing_category) order by pricing_category) type
from product
where finish_product_id = 1
group by id;

See SQLFiddle

#1