如果有一個表叫做變體
id slug slug_id
------- --------- ----------
1 s 10
1 m 10
1 l 10
1 black 11
1 pink 11
1 ship_us 12
1 ship_uk 12
2 xl 10
2 xxl 10
2 blue 11
2 white 11
結果我需要這種格式：
id variant_slug
-------- ------------
1 1-s-black-ship_us
1 1-s-black-ship_uk
1 1-s-pink-ship_us
1 1-s-pink-ship_uk
1 1-m-black-ship_us
1 1-m-black-ship_uk
1 1-m-pink-ship_us
1 1-m-pink-ship_uk
1 1-l-black-ship_us
1 1-l-black-ship_uk
1 1-l-pink-ship_us
1 1-l-pink-ship_uk
2 2-xl-blue
2 2-xl-white
2 2-xxl-blue
2 2-xxl-white
問：這條記錄在同一個變體表中。我幾乎沒有使用UDF、存盤程序的經驗，我需要通過查詢來完成這件事。這是否可能不使用 UDF、SP。
uj5u.com熱心網友回復：
也許是假設長度為 size<=3 的自連接。
Select A.ID
,variant_slug = concat(A.id,'-',A.slug,'-',B.slug)
from YourTable A
Join YourTable B
on A.ID=B.ID
and A.slug<>B.slug
and len(B.slug)>3
and len(A.slug)<=3
結果
ID variant_slug
1 1-l-black
1 1-l-pink
1 1-m-black
1 1-m-pink
1 1-s-black
1 1-s-pink
2 2-xl-blue
2 2-xl-white
2 2-xxl-blue
2 2-xxl-white
而不是len()，您可以列舉大小，即 {NOT } IN ('s','m','l','xl','xxl')
Select A.ID
,variant_slug = concat(A.id,'-',A.slug,'-',B.slug)
from YourTable A
Join YourTable B
on A.ID=B.ID
and A.slug<>B.slug
and B.slug NOT IN ('s','m','l','xl','xxl')
and A.slug IN ('s','m','l','xl','xxl')
uj5u.com熱心網友回復：
您可以INNER JOIN將表分成兩個單獨的表：size，color并獲取變體資料，如下所示：
declare @slug table(id int, slug varchar(100))
insert into @slug values
(1,'s')
,(1,'m')
,(1,'l')
,(1,'black')
,(1,'pink')
,(2,'xl')
,(2,'xxl')
,(2,'blue')
,(2,'white');
SELECT size.id, CONCAT_WS('-',size.id,size.size, color.color) as variant_size
from (select id,slug as size from @slug where slug in ('s','m','l','xl','xxl') ) as size
INNER JOIN (select id,slug as color from @slug where slug NOT in ('s','m','l','xl','xxl') ) as COLOR
on color.id = size.id
| ID | 變體大小 |
|---|---|
| 1 | 1-s-黑色 |
| 1 | 1-s-粉紅色 |
| 1 | 1米黑 |
| 1 | 1米粉紅色 |
| 1 | 1-l-黑色 |
| 1 | 1-l-粉紅色 |
| 2 | 2-xl-藍色 |
| 2 | 2-xl-白色 |
| 2 | 2-xxl-藍色 |
| 2 | 2-xxl-白色 |
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/393894.html
標籤：sql sql-server 查询语句
上一篇：LEN()有錯誤嗎？