目录
题一、 586订单最多的客户
答案:
select customer_number from orders
group by customer_number //按客户名分组
order by count(*) //排序,根据每组中出现的次数排序
desc limit 1; //降序,只取序列中的1位
题二、603 空余的座位
解答:
select distinct c1.seat_id // 去重,显示c1表中的seat_id序列
from cinema c1, cinema c2 // 自连接
//下面一堆条件,为了选出座位连在一起的空闲座位
//明显会有很多重复的,所以输出的时候需要去重
where abs(c1.seat_id - c2.seat_id) = 1 //c1表座位序号,在c2表的前后
and c1.free = 1 // 拿来对比的c1表座位是空闲的
and c2.free = 1 // 确保c2表座位也是空闲的
order by c1.seat_id; //升序
题三、610 判断三角形
解答:
select x,y,z,
if( abs(x)+abs(y)>abs(z)
&& abs(x)+abs(z)>abs(y)
&& abs(z)+abs(y)>abs(x) ,"Yes","No") //sql中的三目运算符 if(条件,满足条件输出,不满足条件输出) 和 ? : 用法一样,abs(数) 取这个数的绝对值
as triangle //按题目要求,起别名
from triangle;
题四、613 直线上的最近距离
解法一:
select min(abs(p1.x-p2.x)) as shortest //将差值取绝对值,然后选出最小的
from point p1, point p2
where p1.x != p2.x; //题目中说点的坐标不会相同
解法二:
select min(abs(p1.x-p2.x)) as shortest //将差值取绝对值,然后选出最小的
from point p1
join point p2 //自连接
on p1.x != p2.x; //筛选条件
题五、619 只出现一次的最大数字
解法一:
select if(count(*)=1,n1.num, null) num //如果这个数只出现了一次,返回这个数,否则输出null
from my_numbers n1
group by n1.num //按num分组查询,并按出现次数排序,然后再按num中最小的倒序,取第一个
order by count(*), min(n1.num) desc limit 1;
解法二:
select max(num) as num
from
(select num //子查询
from my_numbers
group by num //按num分组
having count(*)=1) //在分组中,找到计数为1的,作为一个表,起别名为:t
t;
题六、1075 项目员工I
思路:
- 先查连接表,将两张表连接起来,作为临时表:
- 再从临时表中,按 project_id 作为分组依据,对临时表进行分组查询,求得平均工作年限
- 对分组查询的结果,按 project_id 排序
# Write your MySQL query statement below
select project_id, round(avg(experience_years), 2)
from Project p1
join Employee e1
on p1.employee_id = e1.employee_id
group by project_id
order by project_id;
题七、175 组合两个表
解析:无论 person 是否有地址信息,都要基于上述两表提供信息
由此可知,应该采用左连接或者右连接
(这题中,左连接效率相对高些)
左连接写法:
select p.FirstName, p.LastName, City, State
from Person p
left join Address a
on p.PersonId = a.PersonId;
右连接写法:
select p.FirstName,p.LastName,City,State
from Address a
right join Person p
on p.PersonId = a.PersonId;
题八、176 第二高的薪水
解析:
- 由题意,要查第二高薪水,若无,则要返回一个null
- 由此想到,使用 ifnull( 筛选条件,为空时返回 ) 帮助筛选,若为 null ,则返回null
- 第二高,可能有很多个,使用 distinct 去重
- 按 Salary 排序 使用关键词 order by
- 降序,使用 desc 降序
- 要显示第二个,其实这一步之后,去重关键词可有可无,,,使用 : limit 从第几个开始显示, 显示几个
代码:
select ifnull(
(select distinct Salary
from Employee
order by Salary desc limit 1,1),null) as SecondHighestSalary ;
题九、1777每家商店的产品价格
思路:
- 从题目可知,此为按 “product_id” 分类,即使用 group by
- 最直接的思路,就是子查询,查询每一种store的价格,需要的两个约束条件:store的名称,产品的id
代码:
select product_id,
(select price from Products p1 where store='store1' and p.product_id = p1.product_id ) as store1,
(select price from Products p2 where store='store2' and p.product_id = p2.product_id ) as store2,
(select price from Products p3 where store='store3' and p.product_id = p3.product_id ) as store3
from Products p
group by product_id;