力扣sql简单篇练习(二十六)

力扣sql简单篇练习(二十六)

1 每家商店的产品价格

1.1 题目内容

1.1.1 基本题目信息

1.1.2 示例输入输出

1.2 示例sql语句

# 多行变成多列,考虑用sum if+分组
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 Products
GROUP BY product_id

1.3 运行截图

2 员工的直属部门

2.1 基本题目内容

2.1.1 基本题目信息

2.1.2 示例输入输出

2.2 示例sql语句

# 多种条件可以考虑使用union 进行拼接(要去重)
SELECT employee_id,department_id
FROM Employee
GROUP BY employee_id
HAVING count(department_id)=1
UNION
SELECT employee_id,department_id
FROM Employee
WHERE primary_flag='Y'
GROUP BY employee_id
HAVING count(department_id)>=1

2.3 运行截图

3 没有广告的剧集

3.1 题目内容

3.1.1 基本题目信息1

3.1.2 基本题目信息2

3.1.3 示例输入输出

3.2 示例sql语句

# Write your MySQL query statement below
SELECT  p.session_id
FROM Playback p
LEFT JOIN Ads a
ON p.customer_id=a.customer_id AND a.timestamp between start_time AND end_time
WHERE a.timestamp is null

3.3 运行截图