iT邦幫忙

1

[MYSQL] 資料表 JOIN + GROUP BY 問題

資料表如下:

商品 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欄位就會出現很多重複的值,並且不知道如何在這樣的結構中取得規格價錢最低者。

第一次發求助文,如果敘述不清楚還請見諒
還請各位協助。。感激不盡/images/emoticon/emoticon02.gif

看更多先前的討論...收起先前的討論...
你的資料最好是能夠把 table 結構,跟 資料, 用insert into 的方式,用文字檔貼上來.
你不知道是第幾個來問SQL 問題,用貼圖的.幾乎每個我都要提醒一遍.
這種事情其實不需要提醒,而是你要能夠換位思考,你都懶得貼資料了,貼幾張照片
就應付過去了.別人想回答,也就是寫點概略的code, 要能夠正確的,還要幫做table,測試資料.不過你還算好一點啦,起碼有貼圖,還有些自己都說不清楚的,還要幫他想測試資料的.

另外你問的,你說要最低價,但是卻又出現好幾個價格?
是對應產品的最低價?
這樣的報表格式很不好啦,是以前手工時代的風格.
另外以我的看法,資料庫的查詢產生報表,不是這樣搞成橫列的逗號分隔的.
因為這樣的方式很糟糕,是一些落伍的,不會資訊思考的,用以前的思考方式,
硬要產生的格式,再用眼睛去對,這是不好的方式啦. 指令要寫很快也很簡單,
但是資料量大時,也是這樣一大串嗎? 人工去看,有意義嗎?
yuuerh iT邦新手 5 級 ‧ 2020-07-05 11:14:16 檢舉
感謝回復和告知

最低價是指對應產品中的規格價錢最低者沒錯。
資料是要給PHP輸出api用的,用逗號分開的資料會變成陣列給程式跑

主要目的是希望能列出各項商品中,規格的最低價
並且是以商品分類來區分。
還是能請教看看是否會有更好的處理方向?
我可以再自行研究看看,感謝
group by , 再用 group-concat 結合,就能產生用逗號隔開的字串了.
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat

這是很基本的函數啦. 你先自己兜起來試試.
加油.
> 另外你問的,你說要最低價,但是卻又出現好幾個價格?
> 是對應產品的最低價?

看範例資料像是「同一產品的最低價」
實在不解其精妙之處
1.相同product id, 不同 spec_id 比出最低價有什麼意義(iPhone 64GB 比 iPhone 256GB 便宜?)
2.這個統計結果,跟新產品的上線有關,跟時間有關嗎(如果每個月執行一次,連續執行12次的內容會不一樣嗎?)
3
一級屠豬士
iT邦大師 1 級 ‧ 2020-07-05 13:31:54
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)

還是幫你寫了一下.

這是所謂的刀子口豆腐心嗎
/images/emoticon/emoticon51.gif

yuuerh iT邦新手 5 級 ‧ 2020-07-06 03:34:59 檢舉

感謝前輩解惑!
我對於JOIN用法一直很不熟悉,有長知識了!
/images/emoticon/emoticon07.gif

可以常來討論,記得要貼程式碼跟測試資料.

2
rogeryao
iT邦高手 1 級 ‧ 2020-07-05 13:47:25
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

Demo

yuuerh iT邦新手 5 級 ‧ 2020-07-06 03:38:24 檢舉

感謝解答
不同的寫法也來參考看看
你們都是高手/images/emoticon/emoticon02.gif

2
rexsh
iT邦新手 5 級 ‧ 2020-07-05 15:04:27

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] 

SQLDEMO

yuuerh iT邦新手 5 級 ‧ 2020-07-06 03:39:55 檢舉

感謝各路高手解惑.../images/emoticon/emoticon35.gif

我要發表回答

立即登入回答