資料表如下：
商品 products
| products_id | products_category_id | products_title |
|---|---|---|
| 1 | 1 | XX商品 |
| 2 | 1 | OO商品 |
| 3 | 1 | AA商品 |
| 4 | 2 | PP商品 |
| 5 | 2 | UU商品 |
商品分類 products_category
| category_id | category_title |
|---|---|
| 1 | 分類1 |
| 2 | 分類2 |
商品規格 products_spec
| spec_id | products_id | spec_title | spec_price |
|---|---|---|---|
| 1 | 1 | 規格1 | 1500 |
| 2 | 1 | 規格2 | 1200 |
| 3 | 2 | 規格1 | 800 |
| 4 | 2 | 規格2 | 500 |
| 5 | 3 | 規格1 | 1200 |
| 6 | 4 | 規格1 | 500 |
| 7 | 5 | 規格1 | 300 |
| ... | ... | ... | ... |
我有個需求，希望資料輸出是以下格式，表格後附上欄位說明：
category_id | products_id | min_price
------------|--------
1 | 1,2,3 | 1200,500,1200
2 | 4,5 | 500,300
category_id: 分類ID
products_id: 分類中的每項商品ID，用逗號分開
min_price: 該項商品中，對應產品中的規格價錢最低者
目前的方向是先用LEFT JOIN，再用 group_concat ，程式碼如下：
SELECT `products_category`.`category_id` ,
GROUP_CONCAT(`products`.`products_id`) AS `products_id`
FROM `products_category`
LEFT JOIN `products` ON `products`.`products_category_id` = `products_category`.`category_id`
GROUP BY `products_category`.`category_id`
上面程式碼只LEFT JOIN products資料表都沒問題
但只要再LEFT JOIN products_spec資料表， products_id欄位就會出現很多重複的值，並且不知道如何在這樣的結構中取得規格價錢最低者。
第一次發求助文，如果敘述不清楚還請見諒
還請各位協助。。感激不盡
已邀請的邦友 {{ invite_list.length }}/5
create table it200705_prod (
product_id tinyint not null
, product_category_id tinyint not null
);
insert into it200705_prod values
(1,1),(2,1),(3,1),(4,2),(5,2);
create table it200705_prod_spec (
spec_id tinyint not null
, product_id tinyint not null
, spec_price int not null
);
insert into it200705_prod_spec values
(1,1,1500),
(2,1,1200),
(3,2,800),
(4,2,500),
(5,3,1200),
(6,4,500),
(7,5,300);
---
select c.product_category_id category_id
, group_concat(c.product_id order by c.product_id SEPARATOR ',') products_id
, group_concat(c.min_price order by c.product_id SEPARATOR ',') min_price
from (select a.product_category_id
, a.product_id
, b.min_price
from it200705_prod a
join (select product_id
, min(spec_price) min_price
from it200705_prod_spec
group by product_id) b
on a.product_id = b.product_id) c
group by c.product_category_id;
+-------------+-------------+---------------+
| category_id | products_id | min_price |
+-------------+-------------+---------------+
| 1 | 1,2,3 | 1200,500,1200 |
| 2 | 4,5 | 500,300 |
+-------------+-------------+---------------+
2 rows in set (0.00 sec)
還是幫你寫了一下.
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
MSSQL 來亂的,無參考性
SELECT T.[products_category_id] AS category_id,
STRING_AGG(T.[products_id],',') AS products_id,
STRING_AGG(T.spec_price,',') AS spec_price
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY P1.[products_category_id] ,P1.[products_id] ORDER BY PS.spec_price ) AS RNO ,
P1.[products_category_id] ,P1.[products_id] ,PS.spec_price
FROM [dbo].[products] P1
LEFT JOIN [dbo].[products_spec] PS
ON P1.products_id=PS.products_id ) T WHERE T.RNO=1
GROUP BY T.[products_category_id] ORDER BY T.[products_category_id]
iThome鐵人賽
看影片追技術