day14 SQL基础-连接

1294. 不同国家的天气类型

写一段 SQL 来找到表中每个国家在 2019 年 11 月的天气类型。

天气类型的定义如下：当 weather_state 的平均值小于或等于15返回 Cold，当 weather_state 的平均值大于或等于 25 返回 Hot，否则返回 Warm。

你可以以任意顺序返回你的查询结果。

查询结果格式如下所示：

select
	c.country_name,
	case when avg(w.weather_state) <=  15 then 'Cold'
		 when avg(w.weather_state) >=  25 then 'Hot'
		 else 'Warm' 
	end as weather_type 
from 
		Countries as c left join Weather as w on w.country_id=c.country_id 
# where 
-- 		year(day)='2019' and month(day)='11'
		 date_format(w.day,'%Y%m') = '201911'
group by 
		c.country_name

626. 换座位

编写SQL查询来交换每两个连续的学生的座位号。如果学生的数量是奇数，则最后一个学生的id不交换。

按 id 升序 返回结果表。

查询结果格式如下所示。

方法一：
select
	case when mod(id,2)=1 and id=(select count(*) from Seat) then id
		 when mod(id,2)=1 then id+1
		 else id-1
	end as id,
	student
from 
	Seat 
order by 
	id
方法二：
select 
	row_number() over(order by if(id % 2 = 0 , id - 1 , id + 1)) as id,
    student
from 
	Seat_626
方法三：
select 
	if(id%2=1,if(id=(select count(id) from Seat),id,id+1),id-1) id, student 
from 
	Seat
order by 
	id

1783. 大满贯数量

请写出查询语句，查询出每一个球员赢得大满贯比赛的次数。结果不包含没有赢得比赛的球员的ID 。

结果集 无顺序要求 。

查询结果的格式，如下所示。

方法一：
select 
	player_id,
	player_name,
	count(*) as grand_slams_count 
from
	(select Wimbledon from Championships
	union all
	select Fr_open from Championships
	union all
	select US_open from Championships
	union all
	select Au_open from Championships) as t left join Players as p on p.player_id=t.Wimbledon
group by 
		p.player_id , p.player_name
方法二：
select
	p.player_id,
	p.player_name,
	sum(player_id=wimbledon)+
	sum(player_id=fr_open)+
	sum(player_id=us_open)+
	sum(player_id=au_open) as grand_slams_count 
from
	players p join championships c
group by
	p.player_id, p.player_name
having
	grand_slams_count > 0

1164. 指定日期的产品价格

写一段 SQL来==查找在 2019-08-16 时全部产品的价格，假设所有产品在修改前的价格都是 10 ==。

以 任意顺序 返回结果表。

查询结果格式如下例所示。

-- select 
-- 		p.product_id,
-- -- 	max(new_price)
-- 		case when p.product_id in (select distinct p.product_id from Products as p where change_date <= ' 2019-08-16')
-- 	       then max(new_price) else 10 end as price
-- from
-- 		Products as p
-- group by 
-- 		p.product_id
select 
	a.product_id,
	ifnull(b.new_price,10) as price
		
from 
	(select  distinct product_id from Products) a left join 
		(select 
			product_id,new_price
		 from 
			Products
		 where
			(product_id,change_date) in (
									select 
										product_id,
										max(change_date) as change_date
									from 
										Products
									where
										change_date <= '2019-08-16' 
									group by
										product_id)
					) as b on a.product_id = b.product_id