2252. Dynamic Pivoting of a Table(目前不会)(No)

SQL架构

Table: Products

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| product_id  | int     |
| store       | varchar |
| price       | int     |
+-------------+---------+
(product_id, store) is the primary key for this table.
Each row of this table indicates the price of product_id in store.
There will be at most 30 different stores in the table.
price is the price of the product at this store.

Important note: This problem targets those who have a good experience with SQL. If you are a beginner, we recommend that you skip it for now.

Implement the procedure PivotProducts to reorganize the Products table so that each row has the id of one product and its price in each store. The price should be null if the product is not sold in a store. The columns of the table should contain each store and they should be sorted in lexicographical order.

The procedure should return the table after reorganizing it.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Products table:
+------------+----------+-------+
| product_id | store    | price |
+------------+----------+-------+
| 1          | Shop     | 110   |
| 1          | LC_Store | 100   |
| 2          | Nozama   | 200   |
| 2          | Souq     | 190   |
| 3          | Shop     | 1000  |
| 3          | Souq     | 1900  |
+------------+----------+-------+
Output: 
+------------+----------+--------+------+------+
| product_id | LC_Store | Nozama | Shop | Souq |
+------------+----------+--------+------+------+
| 1          | 100      | null   | 110  | null |
| 2          | null     | 200    | null | 190  |
| 3          | null     | null   | 1000 | 1900 |
+------------+----------+--------+------+------+
Explanation: 
We have 4 stores: Shop, LC_Store, Nozama, and Souq. We first order them lexicographically to be: LC_Store, Nozama, Shop, and Souq.
Now, for product 1, the price in LC_Store is 100 and in Shop is 110. For the other two stores, the product is not sold so we set the price as null.
Similarly, product 2 has a price of 200 in Nozama and 190 in Souq. It is not sold in the other two stores.
For product 3, the price is 1000 in Shop and 1900 in Souq. It is not sold in the other two stores.

别人的代码:

CREATE PROCEDURE PivotProducts()
BEGIN
	# Write your MySQL query statement below.
	SET group_concat_max_len=100000;
SET @sql = NULL;
#SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN store= ''',store,''' THEN price ELSE null END) AS ', store) ORDER BY store)  #case when 和 if 都可以
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(store= ''',store,''', price, null)) AS ', store) ORDER BY store) 
INTO @sql 
FROM Products;

SET @sql = CONCAT('SELECT product_id, ', @sql , ' FROM Products GROUP BY product_id');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
CREATE PROCEDURE PivotProducts()
BEGIN
	# Write your MySQL query statement below.
	declare colCnt int default 0;   -- 需要转换成列的个数
declare cname_i varchar(50);    -- 列名
declare colindex int default 0; -- 当前列

set @sqlStr ='select product_id ';

set colCnt=( select count(distinct store) from products);

while colCnt>colindex do
    set cname_i=(
        select 
            store
        from products
        group by store
        order by store
        limit colindex,1
    );

    set @sqlStr=concat(@sqlStr,',max(if(store=''',cname_i,''',price,null)) as ',cname_i);

    set colindex=colindex+1;
end while;

set @sqlStr =concat(@sqlStr,' from products group by product_id ');

prepare dosql from @sqlStr;
execute dosql;

END

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值