提交(列转行)
select product_id, 'store1' as store, store1 as price from Products where store1 is not null
union
select product_id, 'store2' as store, store2 as price from Products where store2 is not null
union
select product_id, 'store3' as store, store3 as price from Products where store3 is not null;
延申: 行转列
SELECT
product_id,
SUM(IF(store = 'store1', price, NULL)) 'store1',
SUM(IF(store = 'store2', price, NULL)) 'store2',
SUM(IF(store = 'store3', price, NULL)) 'store3'
FROM
Products1
GROUP BY product_id ;
1. 注意: 加引号,不加引号区别?
- 表名和字段名,即使是中文也不用加单引号;
- 对于表格内的value值,要不要加单引号取决于这个字段设置的数据类型, 不加引号是数字类型,加了就是字符串或日期类型了 如果是 int 类型,那么是不需要引号的,不过,加起引号也不会报错,会自动转换类型。
2. IF 表达式
IF( expr1 , expr2 , expr3 )
- expr1 的值为 TRUE,则返回值为 expr2
- expr1 的值为FALSE,则返回值为 expr3
参考文章:
https://leetcode.cn/problems/rearrange-products-TABLE/solutions/1688814/by-esqiimulme-pjiy/
http://t.csdn.cn/AC21F
仅take notes, 如有侵权,请联系删除,感谢!