leetcode力扣MySQL数据库刷题（31 - 45）

（31）511. 游戏玩法分析 I

select player_id,min(event_date) as first_login
from activity
group by player_id

（32）512. 游戏玩法分析 II

select a.player_id,a.device_id 
from activity a
join (select player_id,min(event_date) as first_login from activity group by player_id) t
on a.event_date=t.first_login and a.player_id=t.player_id

（33）1148. 文章浏览 I

select distinct author_id as id 
from views 
where author_id=viewer_id
order by id

（34）1149. 文章浏览 II

select distinct viewer_id as id 
from views
group by viewer_id,view_date
having count(distinct article_id) > 1
order by id

（35）577. 员工奖金

select e.name,b.bonus
from employee e 
left join bonus b 
on e.empid=b.empid 
where bonus<1000 or bonus is null

（36）1633. 各赛事的用户注册率

select contest_id,round(count(user_id)/(select count(*) from users)*100,2) as percentage
from register 
group by contest_id 
order by percentage desc,contest_id

（37）1327. 列出指定时间段内所有的下单产品

select p.product_name,sum(o.unit) as unit
from orders o
join products p 
on o.product_id=p.product_id
where o.order_date between '2020-02-01' and '2020-02-29'
group by o.product_id
having unit>=100

（38）603. 连续空余座位

select distinct(c1.seat_id) 
from cinema c1 
join cinema c2
on abs(c2.seat_id-c1.seat_id)=1
where c1.free=1 and c2.free=1
order by c1.seat_id

（39）183. 从不订购的客户

select c.name as customers
from customers c 
left join orders o 
on c.id=o.customerid 
where o.id is null

（40）607. 销售员

select name
from salesperson
where sales_id not in (select o.sales_id from orders o, company c where o.com_id = c.com_id and c.name='RED')

（41）1294. 不同国家的天气类型

select c.country_name, case when t.avg_wea>=25.0 then 'Hot' when t.avg_wea<=15.0 then 'Cold' when t.avg_wea>15.0 and t.avg_wea<25.0 then 'Warm' end as weather_type
from Countries c
join (select country_id,avg(weather_state) as avg_wea from Weather where day between '2019-11-01' and '2019-11-30' group by country_id) t
on c.country_id=t.country_id

（42）610. 判断三角形

select *,case when x+y>z and x+z>y and y+z>x then 'Yes' else  'No' end as triangle from triangle

（43）1407. 排名靠前的旅行者

select u.name,ifnull(sum(r.distance),0) as travelled_distance
from users u
left join rides r 
on u.id=r.user_id
group by r.user_id
order by travelled_distance desc,u.name

（44）1485. 按日期分组销售产品

select sell_date, count(distinct product) num_sold, group_concat(distinct product order by product asc separator ',') as products
from activities
group by sell_date

（45）1511. 消费者下单频率

select a.customer_id,c.name
from (select customer_id
        from Orders o 
        join Product p 
        using(product_id)
        where month(order_date)=6 or month(order_date)=7
        group by customer_id,month(order_date)
        having sum(quantity*price)>=100) a
join Customers c
using(customer_id)
group by customer_id having count(*)>1;