使用 MySQL 中的透视函数来制作一个先验表

Question

在这种情况下，MySQL将行转换为动态列数，但结果不同，这让我感到困惑。

假设我有 3 张桌子

create table order_match
(
id int(10) PRIMARY KEY not null,
order_status_id int(10) not null
);
create table order_match_detail
(
id int(10) PRIMARY KEY not null,
order_match_id int(10) not null,
product_id int(10) NOT NULL
);
create table product
(
id int(10) PRIMARY KEY not null,
name varchar(255) not null
);
Insert into order_match (id, order_status_id)
select 1, 6 union all
select 2, 7 union all
select 3, 6 union all
select 4, 6;
Insert into order_match_detail (id, order_match_id, product_id)
select 1, 1, 147  union all
select 2, 2, 148 union all
select 3, 3, 147 union all
select 4, 4, 149 union all
select 5, 4, 147;
Insert into product (id, name)
select 147, 'orange' union all
select 148, 'carrot' union all
select 149, 'Apple';

与order_match.id = order_match_detail.order_match_id和order_match_detail.product_id = product.id

所以就像MySQL透视行中的前一种情况一样，我想输入产品名称，交易在order_status_id而不是7中(因为7是过期交易并被拒绝(

the expected results was like this :
id (in order_match)    |    Orange  |  Carrot  |   Apple
1                           1            0           0
3                           1            0           0
4                           1            0           1 

根据以前案例中的解决方案，我使用了这个

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'count(case when product.name = ''',
product.name,
''' then 1 end) AS ',
replace(product.name, ' ', '')
)
) INTO @sql
from product;
SET @sql = CONCAT('SELECT omd.order_match_id, ', @sql, ' from order_match_detail omd
left join order_match om
on omd.order_match_id = om.id
left join product p
on omd.product_id = p.id
where om.order_status_id in (4, 5, 6, 8)
group by omd.order_match_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;

但是idk为什么要返回0，这是不可能的

这是小提琴 https://www.db-fiddle.com/f/nDe3oQ3VdtfS5QDokieHN4/6

Answer 1

对于您的GROUP_CONCAT查询;在您的例子中，stmt，您将产品表称为product本身。但是在您的联接查询中，您将产品表引用为别名p。由于第一个group_concat查询是联接查询的一部分，因此需要保持表别名相同。(在第5行进行了更改(

SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT(
'count(case when p.name = ''',  
product.name,
''' then 1 end) AS ',
replace(product.name, ' ', '')
)
) INTO @pivotsql
from product;
SET @sql = CONCAT('SELECT omd.order_match_id, ', @pivotsql, ' from order_match_detail omd
left join order_match om
on omd.order_match_id = om.id
left join product p
on omd.product_id = p.id
where om.order_status_id in (4, 5, 6, 8)
group by omd.order_match_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;