- 游戏玩法分析 I
思路:使用group by对play_id进行分组(理解为分为了多张表,每个玩家就有一张表),然后查询每组中最小的日期,使用分组函数:min()
# Write your MySQL query statement below
select player_id,min(event_date) as first_login from Activity group by player_id;
- 订单最多的客户
# Write your MySQL query statement below
select customer_number
from Orders
group by customer_number
order by count(customer_number) desc #执行优先级:group by比order by先,所以会先分组再降序排序
limit 1; #题目说恰好有一个客户比任何其他客户下了更多的订单,所以返回一条结果就可以
- 寻找用户推荐人
一开始想的很简单,直接写的下面这个
# Write your MySQL query statement below
select name from customer where referee_id != 2
结果只返回了一个
发现NULL的都没过滤出,然后又写了个
select name from customer where referee_id != 2 or referee_id = NULL;
结果还是一样,看了下官方题解,NULL不能直接用=,要换成is,果然是基础不牢。。。
select name from customer where referee_id != 2 or referee_id is NULL;
- 第二高的薪水
select ifnull(
(select distinct salary from Employee order by salary desc
limit 1 offset 1),NULL) as SecondHighestSalary;
# 加distinct是为了去重,以防第一第二的工资相同,而导致查询结果有问题
必须加distinct,为了去重,以防第一第二的工资相同,而导致查询结果有问题
ifnull(a,b)函数解释:
如果查询的结果存在,结果返回a
如果查询的结果不存在,结果返回b
- 删除重复的电子邮箱
一开始脑子没转过来,P1.id<P2.id这个写成了P1.id<>P2.id,直接把重复的删掉了,一个不剩~
delete P2 from Person as P1,Person as P2 where P2.email=P1.email and P1.id<P2.id;
# delete P2表示删除P2表中满足Where条件的记录
- 合作过至少三次的演员和导演
写一条SQL查询语句获取合作过至少三次的演员和导演的 id 对 (actor_id, director_id)
select actor_id,director_id from ActorDirector group by actor_id,director_id having count(*)>=3;
group by actor_id,director_id可以把actor_id,director_id看成一个整体,以他们的整体来进行分组
- 销售员
select name
from salesPerson
where sales_id not in (select sales_id from Company,Orders where Company.com_id=Orders.com_id and Company.name = 'RED')