iT邦幫忙

6

趣味SQL 又來了! 商品組合問題.

sql
假設我們要辦商品促銷,想得商品的組合.
     
create table it200117a (
  id smallint generated always as identity
, prdct_nme text not null
);

insert into it200117a (prdct_nme) values
('三角鉛筆'),('藍桿鉛筆'),('黃桿鉛筆'),('學習鉛筆'),('代針筆');


corss join 會得到:

select a.prdct_nme
     , b.prdct_nme
  from it200117a a
  cross join it200117a b;

+-----------+-----------+
| prdct_nme | prdct_nme |
+-----------+-----------+
| 三角鉛筆  | 三角鉛筆  |
| 三角鉛筆  | 藍桿鉛筆  |
| 三角鉛筆  | 黃桿鉛筆  |
...
| 代針筆    | 黃桿鉛筆  |
| 代針筆    | 學習鉛筆  |
| 代針筆    | 代針筆    |
+-----------+-----------+
(25 rows)


可以觀察到會出現相同商品的組合.例如 (三角鉛筆,三角鉛筆) 
(代針筆,代針筆).


select a.prdct_nme
     , b.prdct_nme
  from it200117a a
  join it200117a b
    on (a.id <> b.id);

+-----------+-----------+
| prdct_nme | prdct_nme |
+-----------+-----------+
| 三角鉛筆  | 藍桿鉛筆  |
| 三角鉛筆  | 黃桿鉛筆  |
| 三角鉛筆  | 學習鉛筆  |
| 三角鉛筆  | 代針筆    |
| 藍桿鉛筆  | 三角鉛筆  |
...
| 藍桿鉛筆  | 代針筆    |
...
| 學習鉛筆  | 代針筆    |
| 代針筆    | 三角鉛筆  |
| 代針筆    | 藍桿鉛筆  |
| 代針筆    | 黃桿鉛筆  |
| 代針筆    | 學習鉛筆  |
+-----------+-----------+
(20 rows)

這樣雖然沒有出現相同商品的組合,但是可以觀察到
(三角鉛筆,藍桿鉛筆), (藍桿鉛筆,三角鉛筆) 
與
(藍桿鉛筆,代針筆), (代針筆,藍桿鉛筆) 等
順序不同,但是內容一樣的.

若我們想得到所有的組合, 
(三角鉛筆,藍桿鉛筆), (藍桿鉛筆,三角鉛筆) 視為相同組合,只要任意一個就好.
一般是id 小的先,這只是一般習慣,非強制.
類似以下的組合

+-----------+-----------+
| prdct_nme | prdct_nme |
+-----------+-----------+
| 三角鉛筆  | 藍桿鉛筆  |
| 三角鉛筆  | 黃桿鉛筆  |
| 三角鉛筆  | 學習鉛筆  |
| 三角鉛筆  | 代針筆    |
| 藍桿鉛筆  | 黃桿鉛筆  |
| 藍桿鉛筆  | 學習鉛筆  |
| 藍桿鉛筆  | 代針筆    |
| 黃桿鉛筆  | 學習鉛筆  |
| 黃桿鉛筆  | 代針筆    |
| 學習鉛筆  | 代針筆    |
+-----------+-----------+
(10 rows)

當然也可以用其他順序,只要所有商品的組合是一樣的就可以了.

可以看到每個商品出現了4次,就是與其他4個商品組合.

如以下,也是一種.
+-----------+-----------+
| prdct_nme | prdct_nme |
+-----------+-----------+
| 藍桿鉛筆  | 三角鉛筆  |
| 黃桿鉛筆  | 三角鉛筆  |
| 黃桿鉛筆  | 藍桿鉛筆  |
| 學習鉛筆  | 三角鉛筆  |
| 學習鉛筆  | 藍桿鉛筆  |
| 學習鉛筆  | 黃桿鉛筆  |
| 代針筆    | 三角鉛筆  |
| 代針筆    | 藍桿鉛筆  |
| 代針筆    | 黃桿鉛筆  |
| 代針筆    | 學習鉛筆  |
+-----------+-----------+
(10 rows)

這裡粗體出包了...
**請問要如何下query? **

看更多先前的討論...收起先前的討論...
樓主要不要思考用BOM表的概念來處理
組合商品 ID 名稱
對應的子表 商品組合 商品ID
然後去撈BOM 的ID 再去撈 商品組合,會不會比較好
那個,我可以先說答案嘛。
因為這個組合式的sql。我已經玩到不想玩了。
我還是等2天再說答案吧
先忍幾天,看看有沒有一些有意思的SQL出現.
有時候會出現超有創意的,很驚喜.
發發發大。

這是趣味考試啦,並不是樓主不會。只是玩一下考驗自已的功力。
不限任何sql。唯一限制就是只能用sql搞定。不能用程式處理。

看要不要一起來玩看看。這次算第4次了喔!!
1
Anthony_Yang
iT邦新手 4 級 ‧ 2020-01-17 14:45:39
SELECT *
  FROM ( --利用ROW_NUMBER() 相同資料做排序 只選出排序=1的
		SELECT A.idA,A.prdct_nmeA,A.idB,A.prdct_nmeB,A.total
				,ROW_NUMBER() OVER(PARTITION BY A.total ORDER BY A.total) AS 排序
		FROM (
               --相同組合不同位置 id相加一定一樣
				select  a.id AS idA,a.prdct_nme AS prdct_nmeA
					 ,b.id AS idB, b.prdct_nme AS prdct_nmeB
					 ,(a.id+b.id) as total
				  from it200117a a
				  join it200117a b	on (a.id <> b.id) 
				  ) A 
	  ) B
	WHERE B.排序 = 1

相信一定有更佳解
感覺這是土法煉鋼

阿...有BUG
id1 對 id4
以及 id2 對 id3
total一樣都是5
會缺少組合

3
暐翰
iT邦大師 1 級 ‧ 2020-01-17 16:15:00

感謝大大提供題目,藉由這題讓我爬文學到東西 ^_^


以下方式不是我自己想到的邏輯,邏輯來源 :
sql - How to remove same permutations from different mutiple columns - Stack Overflow

因為此範例是自己cross join自己並且有PK ID,所以可以使用以下SQL簡單得到結果

select 
     a.prdct_nme as prdct_nme1
     , b.prdct_nme as prdct_nme2
from it200117a a
cross join it200117a b
where a.id < b.id

注意假如是join其他表格,這個做法是不適用的!

Postgres 12 Demo Test Link | db<>fiddle

不錯喔.有沒發覺到,table裡有順序性的id,會很好處理問題.

1
linyanbin
iT邦新手 5 級 ‧ 2020-01-17 16:25:48
select a.prdct_nme, b.prdct_nme
from it200117a a
join it200117a b
on (a.id < b.id);

SQL Fiddle

不錯喔!

1
firecold
iT邦新手 3 級 ‧ 2020-01-17 16:47:58

感覺樓上好很多...
當參加

select a.prdct_nme, b.prdct_nme
  from it200117a a
  join it200117a b
  on a.id != b.id
  GROUP BY a.id * b.id

SQL Fiddle

看更多先前的回應...收起先前的回應...

你的解法也很棒!

sion iT邦新手 4 級 ‧ 2020-01-20 09:47:29 檢舉

小弟想請教一下大大這段邏輯GROUP BY a.id * b.id
看不太懂....id * id?

firecold iT邦新手 3 級 ‧ 2020-01-20 10:21:33 檢舉

利用乘法ab = ba的關係
得知三角鉛筆藍桿鉛筆 = 藍桿鉛筆三角鉛筆
然後進行group by

firecold iT邦新手 3 級 ‧ 2020-01-20 10:23:51 檢舉

不過這是因為這個規範下ok
商品數多就不行了
所以才說樓上好很多

乘法的做法倒是不錯的點子。
可以應用很多地方。

firecold iT邦新手 3 級 ‧ 2020-01-21 14:14:31 檢舉

感謝星空大

0
ckp6250
iT邦新手 2 級 ‧ 2020-01-17 17:28:58

最爛的在這兒啦,叫我倒數第一名~

with temp AS (
	(SELECT
	prdct_nme,
	@a:=@a+1 AS sss,
	(select prdct_nme from it200117a where id=@a) prdct_cnme
	FROM
	it200117a , (SELECT @a:= 1) AS s)
	union
	(SELECT
	prdct_nme,
	@b:=@b+1 AS sss,
	(select prdct_nme from it200117a where id=@b) prdct_cnme
	FROM
	it200117a , (SELECT @b:= 2) AS s)
	union
	(SELECT
	prdct_nme,
	@c:=@c+1 AS sss,
	(select prdct_nme from it200117a where id=@c) prdct_cnme
	FROM
	it200117a , (SELECT @c:= 3) AS s)
	union
	(SELECT
	prdct_nme,
	@d:=@d+1 AS sss,
	(select prdct_nme from it200117a where id=@d) prdct_cnme
	FROM
	it200117a , (SELECT @d:= 4) AS s)
)
select
	prdct_nme,
	prdct_cnme
from
	temp
where
	not isnull(concat(prdct_nme,prdct_cnme))
order by
	prdct_nme

還有人能比我爛嗎?

哥哥你來啦

ckp6250 iT邦新手 2 級 ‧ 2020-01-17 21:57:05 檢舉

總要有人墊底啊!

我只能說,你太神了。

0
浩瀚星空
iT邦大師 1 級 ‧ 2020-01-21 13:38:55

其實我的答案已經有人寫出來了。

SELECT a.prdct_nme,b.prdct_nme 
FROM `it200117a` AS a
JOIN `it200117a` AS b ON  a.id<b.id

所以我來惡搞一個答案

SELECT a.prdct_nme,b.prdct_nme 
FROM `it200117a` AS a
JOIN `it200117a` AS b ON  a.prdct_nme > b.prdct_nme

其實還可以惡搞更多答案。不過算了。

敲碗

ckp6250 iT邦新手 2 級 ‧ 2020-01-21 15:28:31 檢舉

報告主辦單位,下回出題,應該要增加一些條件,
比如,字數不得少於500個英文字母,
這樣,我就可以得獎啦。

要將程式碼大於500字並不難喔!!!
只要as參數命名長一點就行了。

我要發表回答

立即登入回答