SQL架构
Table: Products
+-------------+---------+ | Column Name | Type | +-------------+---------+ | product_id | int | | store_name1 | int | | store_name2 | int | | : | int | | : | int | | : | int | | store_namen | int | +-------------+---------+ product_id is the primary key for this table. Each row in this table indicates the product's price in n different stores. If the product is not available in a store, the price will be null in that store's column. The names of the stores may change from one testcase to another. There will be at least 1 store and at most 30 stores.
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 UnpivotProducts
to reorganize the Products
table so that each row has the id of one product, the name of a store where it is sold, and its price in that store. If a product is not available in a store, do not include a row with that product_id
and store
combination in the result table. There should be three columns: product_id
, store
, and price
.
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 | LC_Store | Nozama | Shop | Souq | +------------+----------+--------+------+------+ | 1 | 100 | null | 110 | null | | 2 | null | 200 | null | 190 | | 3 | null | null | 1000 | 1900 | +------------+----------+--------+------+------+ Output: +------------+----------+-------+ | product_id | store | price | +------------+----------+-------+ | 1 | LC_Store | 100 | | 1 | Shop | 110 | | 2 | Nozama | 200 | | 2 | Souq | 190 | | 3 | Shop | 1000 | | 3 | Souq | 1900 | +------------+----------+-------+ Explanation: Product 1 is sold in LC_Store and Shop with prices of 100 and 110 respectively. Product 2 is sold in Nozama and Souq with prices of 200 and 190. Product 3 is sold in Shop and Souq with prices of 1000 and 1900.
别人的代码:
CREATE PROCEDURE UnpivotProducts()
BEGIN
DECLARE store_name varchar(100) default '';
DECLARE done INT;
DECLARE product_cursor CURSOR FOR
SELECT column_name FROM information_schema.columns
WHERE table_name = 'Products' AND column_name <> 'product_id';
DECLARE continue handler FOR SQLSTATE '02000' SET done = 1;
SET @sql = '';
OPEN product_cursor;
REPEAT
FETCH product_cursor INTO store_name;
SET @sql = concat(@sql,
'select product_id, ''',
store_name,
''' store,',
store_name,
' price from Products where ',
store_name,
' is not null union ');
UNTIL done
END REPEAT;
CLOSE product_cursor;
SET @sql = substring(@sql, 1, length(@sql) - 7);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END
作者:zuihulu
链接:https://leetcode.cn/problems/dynamic-unpivoting-of-a-table/solution/lie-zhuan-hang-by-zuihulu-w8s7/
来源:力扣(LeetCode)
著作权归作者所有。商业转载请联系作者获得授权,非商业转载请注明出处。
CREATE PROCEDURE UnpivotProducts()
BEGIN
# Write your MySQL query statement below.
set group_concat_max_len=100000;
set @sql=null;
select group_concat(concat('select product_id, ''',column_name,''' store,',column_name,' price from products where ',column_name,' is not null') separator ' union ') into @sql
from (
select distinct column_name
from information_schema.columns
where table_name='Products' and column_name<>'product_id'
) t;
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;
END