SQL查询练习二(From LeetCode)
请选用MySQL进行测试.
1.将男性和女性的工资互换(E)
思路:使用case when进行条件判断,在使用update进行修改
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 update salary 2 set sex = 3 case sex 4 when 'm' then 'f' 5 else 'm' 6 end
2.找出description不是boring且id是奇数的电影(E)
思路:使用where字句进行筛选,并且使用mod进行奇数偶数的判定
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select id,movie,description,rating 2 from cinema 3 where description != 'boring' 4 and mod(id,2) = 1 5 order by rating desc
3.找出不销售Red厂家的推销员(E)
思路:使用子查询先找出销售RED厂家的销售员id,在将外层查询进行not in
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select name 2 from salesperson 3 where sales_id not in( 4 select sales_id 5 from orders o join company c 6 on o.com_id = c.com_id 7 and c.name = 'RED' 8 )
4.找出数据库中相同的行(E)
思路:使用子查询对内层查询按照Email进行分组,统计出大于1的就是重复的值
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select Email from 2 ( 3 select Email,count(*) as num from Person group by Email 4 ) as sub 5 where num>1
5.删除相同的数据(E)
思路:找到两张表相同的Email但是不同的id,把这行数据进行删除
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 delete p1 from Person p1,Person p2 2 where p1.Email = p2.Email 3 and p1.id > p2.id
6.找出经度和纬度不同的2016年的投资金额总和
思路:找出2015年投资相同的记录数,再将经度,维度作为分组的条件进行连接,最后查出结果
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select sum(insurance.TIV_2016) as tiv_2016 from insurance where insurance.TIV_2015 in ( select TIV_2015 from insurance group by TIV_2015 having count(*) > 1 ) and concat(lat,lon) in ( select concat(lat,lon) from insurance group by lat,lon having count(*) =1 )
7.找出部门平均工资和公司平均工资的高低
思路:计算公司每月的平均薪水,计算部门每月的平均薪水,然后进行比较
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select department_sal.pay_month,department_id, case when department_avg > company_avg then 'higher' when department_avg < company_avg then 'lower' else 'same' end as comparison from ( select department_id,avg(amount) as department_avg,date_format(pay_date,'%Y-%m') as pay_month from salary join employee on salary.employee_id = employee.employee_id group by department_id,pay_month ) as department_sal join ( select avg(amount) as company_avg,date_format(pay_date,'%Y-%m') as pay_month from salary group by pay_month ) as company_sal on department_sal.pay_month = company_sal.pay_month
8.找出谁是facebook上最受欢迎的人
思路:根据request_id和accepter_id,可知3收到两个accept,发送一个request,所以3才是社交最活跃的,采用union all将requester_id,sender_id集合起来,分组才能找到使用最频繁的用户
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select ids as id,cnt as num 2 from 3 ( 4 select ids,count(*) as cnt 5 from 6 ( 7 select requester_id as ids from request_accepted 8 union all 9 select accepter_id from request_accepted 10 ) as tb1 11 group by ids 12 ) as tb2 13 order by num desc 14 limit 1
9.找出followee和follower
思路:B和D都在follower中,B的Follower是C,D,A不在follower中,D的follower是E
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select f1.follower,count(distinct f2.follower) as num 2 from follow f1 join follow f2 3 on f1.follower = f2.followee 4 group by f1.follower
10.找出体育馆人数大于100且连续的天数大于3天的数据
思路:先找出所有大于100人的条件,在使用3次自连接,根据t1,t2,t3的id来进行排序
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
1 select distinct t1.* 2 from stadium t1,stadium t2,stadium t3 3 where t1.people >= 100 and t2.people >= 100 and t3.people >= 100 4 and 5 ( 6 (t1.id-t2.id = 1 and t1.id-t3.id = 2 and t2.id-t3.id = 1) 7 or 8 (t2.id-t1.id = 1 and t2.id-t3.id = 2 and t1.id-t3.id = 1) 9 or 10 (t3.id-t2.id = 1 and t2.id-t1.id = 1 and t3.id-t1.id = 2) 11 ) 12 order by t1.id