Leetcode Mysql 1777. 每家商店的产品价格（DAY 5)

缺乏、安全感 2022-09-14 02:43 168阅读 0赞

### 文章目录 ###

*   *  原题题目
     *  代码实现（首刷自解）
     *  代码实现（首刷理解SUM IF）

--------------------

## 原题题目 ##

--------------------

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATG92ZSA2_size_20_color_FFFFFF_t_70_g_se_x_16]

--------------------

## 代码实现（首刷自解） ##

--------------------

# Write your MySQL query statement below
    SELECT
        p.product_id,
        s1.price store1,
        s2.price store2,
        s3.price store3
    FROM
        (SELECT distinct product_id FROM products ) p
    left join 
        (SELECT product_id,price FROM products WHERE store = 'store1') s1 on p.product_id = s1.product_id
    left join 
        (SELECT product_id,price FROM products WHERE store = 'store2') s2 on p.product_id = s2.product_id
    left join 
        (SELECT product_id,price FROM products WHERE store = 'store3') s3 on p.product_id = s3.product_id

--------------------

## 代码实现（首刷理解SUM IF） ##

--------------------

# Write your MySQL query statement below
    SELECT
        p.product_id,
        SUM(if(store = 'store1' and price is not null,price,null)) store1,
        SUM(if(store = 'store2' and price is not null,price,null)) store2,
        SUM(if(store = 'store3' and price is not null,price,null)) store3
    FROM
        products p
    GROUP BY
        1

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBATG92ZSA2_size_20_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/9603de5f22d9498abfd03c54321c3627.png