Mysql -联合查询
Mysql -联合查询
提问于 2019-07-19 16:29:21
我有一个产品表，如下所示，一个产品可以有3个不同的包装尺寸和价格：
id | packsize1 | price1 | packsize2 | price2 | packsize3 | price3
-------------------------------------------------------------------------------
1 | 1g | 10.00 | 5g | 25.00 | 10g | 50.00
2 | 1g | 12.00 | 5g | 27.00 | 10g | 55.00
3 | 5g | 30.00 | 10g | 40.00 | 50g | 60.00
4 | 2g | 20.00 | 5g | 35.00 | 50g | 60.00
5 | 1g | 10.00 | 5g | 35.00 | 10g | 60.00如何返回包装尺寸和价格仅为1g、5g和10g量化的产品清单如下：
id | packsize1 | price1 | packsize2 | price2 | packsize3 | price3
-------------------------------------------------------------------------------
1 | 1g | 10.00 | 5g | 25.00 | 10g | 50.00
2 | 1g | 12.00 | 5g | 27.00 | 10g | 55.00
3 | 1g | null | 5g | 30.00 | 10g | 40.00
4 | 1g | null | 5g | 35.00 | 10g | null
5 | 1g | 10.00 | 5g | 35.00 | 10g | 60.00回答 3
Stack Overflow用户
回答已采纳
发布于 2019-07-19 16:41:06
您可以解除和重新枢轴的数据。在MySQL中，如下所示：
select id,
'1g' as packsize_1g,
max(case when packsize = '1g' then price end) as price_1g,
'5g' as packsize_5g,
max(case when packsize = '5g' then price end) as price_5g,
'10g' as packsize_10g,
max(case when packsize = '10g' then price end) as price_10g,
from ((select id, packsize1 as packsize, price1 as price
from t
) union all
(select id, packsize2, price2
from t
) union all
(select id, packsize3, price3
from t
)
) t
where packsize in ('1g', '5g', '10g')
group by id;Stack Overflow用户
发布于 2019-07-19 16:38:37
标准化的设计可能是这样的。从现在开始，问题很小.
+----+----------+----------+-------+
| id | packsize | weight_g | price |
+----+----------+----------+-------+
| 1 | 1 | 1 | 10.00 |
| 2 | 1 | 1 | 12.00 |
| 3 | 1 | 5 | 30.00 |
| 4 | 1 | 2 | 20.00 |
| 5 | 1 | 1 | 10.00 |
| 1 | 2 | 5 | 25.00 |
| 2 | 2 | 5 | 27.00 |
| 3 | 2 | 10 | 40.00 |
| 4 | 2 | 5 | 35.00 |
| 5 | 2 | 5 | 35.00 |
| 1 | 3 | 10 | 50.00 |
| 2 | 3 | 10 | 55.00 |
| 3 | 3 | 50 | 60.00 |
| 4 | 3 | 50 | 60.00 |
| 5 | 3 | 10 | 60.00 |
+----+----------+----------+-------+Stack Overflow用户
发布于 2019-07-19 16:43:23
试试这个-
SELECT ID,
CASE WHEN packsize1 = '1g' THEN packsize1 WHEN packsize2 = '1g' THEN packsize2 WHEN packsize3 = '1g' THEN packsize3 END packsize1,
CASE WHEN packsize1 = '1g' THEN price1 WHEN packsize2 = '1g' THEN price2 WHEN packsize3 = '1g' THEN price3 END price1,
CASE WHEN packsize1 = '5g' THEN packsize1 WHEN packsize2 = '5g' THEN packsize2 WHEN packsize3 = '5g' THEN packsize3 END packsize2,
CASE WHEN packsize1 = '5g' THEN price1 WHEN packsize2 = '5g' THEN price2 WHEN packsize3 = '5g' THEN price3 END price2,
CASE WHEN packsize1 = '10g' THEN packsize1 WHEN packsize2 = '10g' THEN packsize2 WHEN packsize3 = '10g' THEN packsize3 END packsize3,
CASE WHEN packsize1 = '10g' THEN price1 WHEN packsize2 = '10g' THEN price2 WHEN packsize3 = '10g' THEN price3 END price3
FROM product页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/57116414
复制相关文章
相似问题
腾讯云开发者
