MySQL 5.5

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table products(products_id varchar(100), products_category_id varchar(100), products_title nvarchar(100));  

insert into products values('1','1','XX商品');
insert into products values('3','1','AA商品');
insert into products values('2','1','OO商品');
insert into products values('5','2','UU商品');
insert into products values('4','2','PP商品');
insert into products values('8','1','GG商品');
insert into products values('9','1','GG商品');

select *
from products
order by products_id,products_category_id;


products_id
products_category_id
products_title


1
1
XX商品

2
1
OO商品

3
1
AA商品

4
2
PP商品

5
2
UU商品

8
1
GG商品

9
1
GG商品


create table products_category(category_id varchar(100), category_title varchar(100)); 

insert into products_category values('2','分類2');
insert into products_category values('1','分類1');

select *
from products_category
order by category_id;


category_id
category_title


1
分類1

2
分類2


create table products_spec(spec_id varchar(100), products_id varchar(100), spec_title nvarchar(100),spec_price int);  

insert into products_spec values('1','1','規格1',1500);
insert into products_spec values('3','2','規格1',800);
insert into products_spec values('2','1','規格2',1200);
insert into products_spec values('5','3','規格1',1200);
insert into products_spec values('4','2','規格2',500);
insert into products_spec values('7','5','規格1',300);
insert into products_spec values('6','4','規格1',500);
insert into products_spec values('10','8','規格1',0);

select *
from products_spec
order by spec_id,products_id;


spec_id
products_id
spec_title
spec_price


1
1
規格1
1500

10
8
規格1
0

2
1
規格2
1200

3
2
規格1
800

4
2
規格2
500

5
3
規格1
1200

6
4
規格1
500

7
5
規格1
300


select a.category_id,
group_concat(b.products_id_price order by a.category_id,b.products_id) as products_id_price_new
from products_category as a
left join (
select x.products_category_id,x.products_id,y.spec_price,
concat_ws('/',x.products_id,cast(min(y.spec_price) as char)) as products_id_price
from products as x 
left join products_spec as y on y.products_id=x.products_id
where y.spec_price>0
group by x.products_category_id,x.products_id
) as b on a.category_id= b.products_category_id
group by a.category_id
order by a.category_id


category_id
products_id_price_new


1
1/1200,2/500,3/1200

2
4/500,5/300

products_id	products_category_id	products_title
1	1	XX商品
2	1	OO商品
3	1	AA商品
4	2	PP商品
5	2	UU商品
8	1	GG商品
9	1	GG商品

spec_id	products_id	spec_title	spec_price
1	1	規格1	1500
10	8	規格1	0
2	1	規格2	1200
3	2	規格1	800
4	2	規格2	500
5	3	規格1	1200
6	4	規格1	500
7	5	規格1	300