问题遇到的现象和发生背景
相关的表结构如下
问题相关代码
-- 每个sku有哪些规格值
SELECT sku.spu_id AS spuid,sku.id AS skuid,sku.`name` skuname,spec.id as specid,spec.`name` specname,o.id AS optionid,o.`name` as optionname FROM a_sku sku
LEFT JOIN a_sku_option so ON sku.id=so.sku_id
LEFT JOIN a_option o ON so.option_id=o.id
LEFT JOIN a_specific spec ON o.specific_id=spec.id
ORDER BY sku.`name`;
结果
运行结果及报错内容
现在可以查到每个sku有多少规格,但是有重复。也就是说我不知道怎么列转行。
我的解答思路和尝试过的方法
应该不能用case when,因为不确定有多少个、哪些规格
尝试写出下面这个语句也不是我想要的
SELECT sku.spu_id AS spuid,sku.id AS skuid,sku.`name` skuname,spec.id as specid,spec.`name` specname,o.id AS optionid,o.`name` as optionname FROM a_sku sku
LEFT JOIN a_sku_option so ON sku.id=so.sku_id
LEFT JOIN a_option o ON so.option_id=o.id
LEFT JOIN a_specific spec ON o.specific_id=spec.id
WHERE sku.spu_id=1
AND (CASE spec.id
WHEN 1 THEN
o.id=2
WHEN 2 THEN
o.id=4
WHEN 3 THEN
o.id=6
WHEN 4 THEN
o.id=8
WHEN 5 THEN
o.id=10
WHEN 6 THEN
o.id=12
END);
结果
我想要达到的结果
跟淘宝京东一样,选择各个规格选项后确定唯一的sku