当前位置：首页 > article >正文

MySQL高阶1777-每家商店的产品价格

article 2024/9/20 13:54:06

题目

找出每种产品在各个商店中的价格。

可以以 任何顺序 输出结果。

准备数据

create database csdn;
use csdn;

Create table If Not Exists Products (product_id int, store ENUM('store1', 'store2', 'store3'), price int);
Truncate table Products;
insert into Products (product_id, store, price) values ('0', 'store1', '95');
insert into Products (product_id, store, price) values ('0', 'store3', '105');
insert into Products (product_id, store, price) values ('0', 'store2', '100');
insert into Products (product_id, store, price) values ('1', 'store1', '70');
insert into Products (product_id, store, price) values ('1', 'store3', '80');

分析数据

第一步:利用if函数将数据拉宽

select
    product_id,
    if(store = 'store1',price,null) as store1,
    if(store = 'store2',price,null) as store2,
    if(store = 'store3',price,null) as store3
from Products;

第二步:最后根据id分组,进行价格统计

select
    product_id,
    sum(if(store = 'store1',price,null)) as store1,
    sum(if(store = 'store2',price,null)) as store2,
    sum(if(store = 'store3',price,null)) as store3
from Products
group by product_id;