好吧,我用这个拉头发。
我有一个名为products_bind_variant
的表itemId int(10)
productId int(10)
variantId int(10)
变体引用类型,无论是大小,颜色等。
此表是变体
variantId int(10)
name varchar(150)
grouping varchar(150)
分组值类似于大小,颜色,范围等
我的变体tavble数据在这里:
|1|Small|Size
|2|Medium|Size
|3|Large|Size
|4|Red|Colour
|5|White|Colour
|6|Blue|Colour
|7|Extra Large|Size
|8|1-3 years|Range
|9|3-10|Range
我的products_bind_variant数据在这里:
|1|69|5
|7|69|6
|8|69|8
|9|69|9
|10|69|4
|12|69|1
|18|69|3
|19|69|2
|20|69|7
我想查询自己在本身上制定variantid的组合,并且仅显示同一组组合的ID。
我尝试使用
SELECT a.productsid,
a.variantid,
b.variantid
FROM products_bind_variant a
INNER JOIN products_bind_variant b
ON a.variantid < b.variantid
但是,返回36个结果,因为它与自身匹配。我需要考虑到仅查找分组类型之间的组合,这将是24种组合。
基本上类似于查询IDS。
so
69| 1,4,8
小,红色,1-3岁
这是我需要生成的组合,而不是名称,variantid
Blue, 1-3 Years, Extra Large
Blue, 3-10 Years, Extra Large
Blue, 1-3 Years, Large
Blue, 3-10 Years, Large
Blue, 1-3 Years, Medium
Blue, 3-10 Years, Medium
Blue, 1-3 Years, Small
Blue, 3-10 Years, Small
Red, 1-3 Years, Extra Large
Red, 3-10 Years, Extra Large
Red, 1-3 Years, Large
Red, 3-10 Years, Large
Red, 1-3 Years, Medium
Red, 3-10 Years, Medium
Red, 1-3 Years, Small
Red, 3-10 Years, Small
White, 1-3 Years, Extra Large
White, 3-10 Years, Extra Large
White, 1-3 Years, Large
White, 3-10 Years, Large
White, 1-3 Years, Medium
White, 3-10 Years, Medium
White, 1-3 Years, Small
White, 3-10 Years, Small
任何帮助将不胜感激。
这将产生24行:
select
`colour`, `range`, `size`, cid, sid, rid
from (select variantId cid, name `colour` from variant where grouping = 'Colour') c
cross join (select variantId sid, name `size` from variant where grouping = 'Size') s
cross join (select variantId rid, name `range` from variant where grouping = 'Range') r
+----+--------+-----------+-------------+-----+-----+-----+
| | colour | range | size | cid | sid | rid |
+----+--------+-----------+-------------+-----+-----+-----+
| 1 | Red | 1-3 years | Small | 4 | 1 | 8 |
| 2 | White | 1-3 years | Small | 5 | 1 | 8 |
| 3 | Blue | 1-3 years | Small | 6 | 1 | 8 |
| 4 | Red | 1-3 years | Medium | 4 | 2 | 8 |
| 5 | White | 1-3 years | Medium | 5 | 2 | 8 |
| 6 | Blue | 1-3 years | Medium | 6 | 2 | 8 |
| 7 | Red | 1-3 years | Large | 4 | 3 | 8 |
| 8 | White | 1-3 years | Large | 5 | 3 | 8 |
| 9 | Blue | 1-3 years | Large | 6 | 3 | 8 |
| 10 | Red | 1-3 years | Extra Large | 4 | 7 | 8 |
| 11 | White | 1-3 years | Extra Large | 5 | 7 | 8 |
| 12 | Blue | 1-3 years | Extra Large | 6 | 7 | 8 |
| 13 | Red | 3-10 | Small | 4 | 1 | 9 |
| 14 | White | 3-10 | Small | 5 | 1 | 9 |
| 15 | Blue | 3-10 | Small | 6 | 1 | 9 |
| 16 | Red | 3-10 | Medium | 4 | 2 | 9 |
| 17 | White | 3-10 | Medium | 5 | 2 | 9 |
| 18 | Blue | 3-10 | Medium | 6 | 2 | 9 |
| 19 | Red | 3-10 | Large | 4 | 3 | 9 |
| 20 | White | 3-10 | Large | 5 | 3 | 9 |
| 21 | Blue | 3-10 | Large | 6 | 3 | 9 |
| 22 | Red | 3-10 | Extra Large | 4 | 7 | 9 |
| 23 | White | 3-10 | Extra Large | 5 | 7 | 9 |
| 24 | Blue | 3-10 | Extra Large | 6 | 7 | 9 |
+----+--------+-----------+-------------+-----+-----+-----+
唯一的pructifid的变体表中有9行:3种颜色,2个范围和4个尺寸
SELECT
b.productid, v.grouping, group_concat(v.name order by v.name separator ',') as variants
FROM products_bind_variant b
inner join variant v on b.variantid = v.variantid
group by b.productid, v.grouping
+----+-----------+----------+--------------------------------+
| | productid | grouping | variants |
+----+-----------+----------+--------------------------------+
| 1 | 69 | Colour | Blue,Red,White |
| 2 | 69 | Range | 1-3 years,3-10 |
| 3 | 69 | Size | Extra Large,Large,Medium,Small |
+----+-----------+----------+--------------------------------+
使用的数据:
DROP TABLE IF EXISTS products_bind_variant;
CREATE TABLE products_bind_variant(
itemId INTEGER NOT NULL
,productId INTEGER NOT NULL
,variantId INTEGER NOT NULL
);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (1,69,5);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (7,69,6);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (8,69,8);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (9,69,9);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (10,69,4);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (12,69,1);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (18,69,3);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (19,69,2);
INSERT INTO products_bind_variant(itemId,productId,variantId) VALUES (20,69,7);
DROP TABLE if exists variant;
CREATE TABLE variant(
variantId INTEGER NOT NULL
,name VARCHAR(11) NOT NULL
,grouping VARCHAR(6) NOT NULL
);
INSERT INTO variant(variantId,name,grouping) VALUES (1,'Small','Size');
INSERT INTO variant(variantId,name,grouping) VALUES (2,'Medium','Size');
INSERT INTO variant(variantId,name,grouping) VALUES (3,'Large','Size');
INSERT INTO variant(variantId,name,grouping) VALUES (4,'Red','Colour');
INSERT INTO variant(variantId,name,grouping) VALUES (5,'White','Colour');
INSERT INTO variant(variantId,name,grouping) VALUES (6,'Blue','Colour');
INSERT INTO variant(variantId,name,grouping) VALUES (7,'Extra Large','Size');
INSERT INTO variant(variantId,name,grouping) VALUES (8,'1-3 years','Range');
INSERT INTO variant(variantId,name,grouping) VALUES (9,'3-10','Range');
如果我正确理解,则需要每个产品分组的所有变体。如果是这样,请思考聚合而不是JOIN
:
SELECT v.grouping,
pbv.productsid,
grouup_concat(pbv.variantid) as variants
FROM products_bind_variant pbv JOIN
variant v
ON pvb1.variantid = v1.variantid
GROUP BY v.grouping;