我有一个包含产品的表,一个包含四个制造商的表和一个中间表。
我想导出一个产品列表,这些产品可能有也可能没有多个制造商。
我想将每个产品导出为一行,右侧有四列(每个制造商一列(。
产品表
|----|------|
| id | name |
|----|------|
| 12 | foo |
|----|------|
product_manufacturer表
|----|------------|------------------|---------------------------|
| id | product_id | manufacturer_id | manufacturer_product_code |
|----|------------|------------------|---------------------------|
| XX | 12 | ABCD | X1X2 |
|----|------------|------------------|---------------------------|
| YY | 12 | LMKO | AAAB |
|----|------------|------------------|---------------------------|
期望的结果:
+------+-------------+--------------------+--------------------+--------------------+--------------------+
| name | internal_id | manufacturer1_code | manufacturer2_code | manufacturer3_code | manufacturer4_code |
+------+-------------+--------------------+--------------------+--------------------+--------------------+
| foo | 12 | X1X2 | null | AAAB | null |
+------+-------------+--------------------+--------------------+--------------------+--------------------+
我试过这个:
SELECT p.`name` AS product, p.`id` AS internal_id,
(
SELECT pm.`manufacturer_product_code`
FROM `product_manufacturer` pm
WHERE pm.`manufacturer_id` internal_id
AND pm.`manufacturer_id` LIKE 'ABCD'
) AS manufacturer1_code,
(
SELECT pm.`manufacturer_product_code`
FROM `product_manufacturer` pm
WHERE pm.`manufacturer_id` LIKE internal_id
AND pm.`manufacturer_id` LIKE 'CDCD'
) AS manufacturer2_code,
(
SELECT pm.`manufacturer_product_code`
FROM `product_manufacturer` pm
WHERE pm.`manufacturer_id` LIKE internal_id
AND pm.`manufacturer_id` LIKE 'LMKO'
) AS manufacturer3_code,
(
SELECT pm.`manufacturer_product_code`
FROM `product_manufacturer` pm
WHERE pm.`manufacturer_id` LIKE internal_id
AND pm.`manufacturer_id` LIKE 'RSRS'
) AS manufacturer4_code
FROM `product_manufacturer` pm
INNER JOIN `products` p
ON p.`id`=pm.`product_id`
这很好地返回了前两列,但其他四列返回 null。
当我让它工作时,我应该使用Group by p.name
正确的方法是什么?谢谢。
一种方法
是通过row_number模拟
DROP TABLE IF EXISTS T,T1;
CREATE TABLE T (ID INT, NAME VARCHAR(10));
CREATE TABLE T1( id varchar(2), product_id int, manufacturer_id varchar(20), manufacturer_product_code varchar(20));
insert into t values(12,'foo');
insert into t1 values
('XX' , 12 , 'ABCD' , 'X1X2'),
('YY' , 12 , 'LMKO' , 'AAAB');
select t.name,t.id,
max(case when rownumber = 1 then manufacturer_product_code else '' end) man1,
max(case when rownumber = 2 then manufacturer_product_code else '' end) man2,
max(case when rownumber = 3 then manufacturer_product_code else '' end) man3,
max(case when rownumber = 4 then manufacturer_product_code else '' end) man4
from t
join
(
select t1.*,
if(t1.product_id <> @p,@rn:=1,@rn:=@rn+1) rownumber,
@p:=t1.product_id p
from t1
cross join (select @rn:=0,@p:=0) r
order by t1.product_id,t1.id
) s
on s.product_id = t.id
group by t.name,t.id;
在此查询中,在子查询中分配行号,在外部查询中使用条件聚合将制造商分配到列。
+------+------+------+------+------+------+
| name | id | man1 | man2 | man3 | man4 |
+------+------+------+------+------+------+
| foo | 12 | X1X2 | AAAB | | |
+------+------+------+------+------+------+
1 row in set (0.00 sec)
如果您有版本 8 或更高版本,您可以使用 row_number 窗口函数,而不是在子查询中使用变量进行模拟。