网上写写题 提高下自己的能力。

Mysql平时写的是真的很少，所以训练一下下。

 

1.查找重复的电子邮箱

我的解法：

select distinct(t1.Email) from Person t1,Person t2 where t1.Id != t2.Id and t1.Email = t2.Email;

官方题解：

select email from Person group by email having count(email) > 1;

学习了下having的用法。（在此之前没听过。。）

where 是检索前的筛选，而having是检索后的组再筛选。

WHERE 子句作用于表和视图，HAVING 子句作用于组。

 

2.大的国家

我的解法：

select t1.name,t1.population,t1.area from world t1 where t1.area>3000000 or t1.population>25000000;

 

3.有趣的电影

我的解法：

select * from cinema t1 where t1.description != 'boring' and t1.id % 2 != 0 group by rating desc;

官方解法：

select * from cinema where mod(id, 2) = 1 and description != 'boring' order by rating DESC;

 

4.组合两个表

我的解法：

select t1.firstname,t1.lastname,t2.city,t2.state from person t1 left join address t2 on t1.personid = t2.personid;

这题就是考 left join的用法。

我一开始写错了。。写成join left 而且不知道要加on。。

 

5.交换工资

这题考的是  case then的用法。。但是我忘了。。就。。

官方解法：

update salary set sex = case sex when 'm' then 'f' else 'm' End;

 

6.超过经理收入的员工

我的解法：

select t1.name as Employee from Employee t1,Employee t2 where t1.managerid = t2.id and t1.salary > t2.salary;

别人的解法：

select t1.name Employee from Employee as t1 where salary > (select salary from Employee where id = t1.managerid);

学习一下嵌套查询。

 

7.从不订购的客户

emm...基础太差。

用left join 代替 exists

别人的解法：

select t1.Name Customers from Customers t1 left join orders t2 on t1.id = t2.customerid where t2.id is null;

 

8.删除重复的电子邮箱

看了下题解。。

解法：

delete t1 from person t1,person t2 where t1.email = t2.email and t1.id > t2.id;

 

9.上升的温度

我的解法：

select t.id from weather t,weather t2 where t.RecordDate = t2.RecordDate + 1 and t.temperature > t2.temperature;

但是这个解法过不了。应该是时间计算的问题。

测试了一下，直接相减的值。

分析一下，估计是先转换成整数再相减，比如2019-6-17 14:33:37-2019-6-17 14:32:47 = 2019617143337-2019617143247 = 90。

怎么说呢，如果日期值是YYYY-MM-DD型 且同一个月的话 直接相减可以算天数。 但是还是用datediff靠谱。因为数据不可控。

 

然后看了下官解：

select t.id from weather t JOIN weather t2 ON  datediff(t.RecordDate,t2.RecordDate) = 1 and t.temperature > t2.temperature;

一个新认识的函数吧。datediff。

 

10.超过5名学生的课

基础太差太差了。

解法：

select class from courses group by class having count(distinct student) > 4;

group by 用的不熟练 having 也不熟练 考虑的点不全面。

 

11.第二高的薪水

解法：

select (select distinct Salary from Employee group by Salary desc limit 1,1) as SecondHighestSalary;

这题我缺的几个点：

1.limit 1 offset 1(基础问题) limit 省略写法的话 第一个参数是偏移量 第二个参数是返回数量

2.分析问题，看了别人表示要去考虑相同第一的问题，因此加了个distinct。解决办法是自己想的，但是分析不是自己分析的。

3.无值时取null，再select一次。

 

目前都是简单难度的题。

接下来写中等难度的。

 

12.第N高的薪水

解法一：

类似与上一题

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

SET N = N-1;# 这个更改值要先改 不能再limit那里进行运算 那里只接受常数

RETURN (

　　# Write your MySQL query statement below.

　　select distinct salary from employee order by salary desc limit N, 1

);

END

 

解法二：

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT

BEGIN

RETURN (

　　# Write your MySQL query statement below.

　　select max(t1.Salary) from Employee t1

　　where N-1 = (

　　　　select count(distinct t2.Salary) from Employee t2 where t2.Salary > t1.Salary

　　)

);

END

这个解法的意思呢就是 让N-1条t2数据都大于t1 然后返回剩下数据中的最大值

这个解法就是不用在BEGIN后写东西。但是效率好像不是很高。

顺便学习一下sql中函数的写法。

 

13.分数排名

解法：

select Score,(select count(distinct t2.Score) from Scores t2 where t2.score >= s.score) AS Rank from Scores s order by score desc;

要学会用count。

挺神奇的吧。

 

14.连续出现的数字

我的笨解法：

select distinct t1.Num as ConsecutiveNums from Logs t1,Logs t2,Logs t3

where t1.id = t2.id+1 and t2.id = t3.id+1 and t1.Num=t2.Num and t2.Num = t3.Num;

 

大佬的解法：

select distinct Num as ConsecutiveNums

from (

　　select Num,

　　case

　　　　when @prev = Num then @count := @count + 1

　　　　when (@prev := Num) is not null then @count := 1

　　end as CNT

　　from Logs, (select @prev := null,@count := null) as t

) as temp

where temp.CNT >= 3

晚点补一下解释~

来啦老弟~

中间的那个select里有2个自定义的变量，一个用于保存数值，一个用于计数。

然后返回对应数值的计数，筛出计数值大于三的数值即可

 

15.部门工资最高的员工

解法：

select t1.Name as Department,t2.Name as Employee,t2.Salary

from Department t1,Employee t2  where t1.id = t2.DepartmentId and

(t2.Salary,t2.DepartmentId) in (select max(Salary),DepartmentId from Employee group by DepartmentId)

最后一行 筛出每个部门最高工资，然后查出工资与部门对应的员工。

 

16.换座位

解法：

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;

换一种思维方式，交换id。

学习了~

 

难度提升~

17.部门工资前三高的员工

解法：

 

select t1.name as Department,t2.name as Employee,t2.salary as salary

from employee t2

inner join department t1

on t1.id = t2.departmentid

where (

　　select count(distinct salary)

　　from employee t3

　　where t3.departmentid = t2.departmentid

　　and t3.salary >= t2.salary

) <= 3

order by departmentid,salary desc

 

18.行程和用户

解法：

select tb.Day Day,Round(count(tb.st!='completed' or null) / count(tb.Day),2) as 'Cancellation Rate' from

(

select request_at Day,id,status as st

from trips t1 inner join users t2

on t1.client_id = t2.users_id and t2.banned = 'No'

where request_at >= '2013-10-01' and request_at <= '2013-10-03'

) as tb

group by tb.Day

上面写多了个嵌套，更新一下：

select request_at Day,Round(count(status!='completed' or null) / count(status),2) as 'Cancellation Rate'

from trips t1 inner join users t2

on t1.client_id = t2.users_id and t2.banned = 'No'

where request_at >= '2013-10-01' and request_at <= '2013-10-03'

group by request_at

想了半天，有头绪不会写。

最后才知道count可以加条件 但是要接个or null

 

19.体育馆的人流量

解法：

select distinct t1.id,t1.visit_date,t1.people from stadium t1,stadium t2,stadium t3

where ((t1.id = t2.id - 1 and t2.id = t3.id-1)  or

　　 　(t1.id = t2.id + 1 and t2.id = t3.id+1) or

　　     (t2.id = t1.id - 1 and t3.id = t1.id+1))

and t1.people >= 100 and t2.people >=100 and t3.people>=100

order by id

 嗯。。把三个判断条件都加上，筛出来就好了。但是我觉得这个解法实在是太暴力了。。而且如果有多个连续不容易列举。

所以我在想能不能 如果有连续，给一个递增值，如果断了，从1开始递增。然后判断值为1 or 2的那一行。分别 id+2=3 id+1=3。

这样写，多个连续时筛选条件就是 n.id = (t.id+n-t.num) and n.num=n

晚点试试能不能行~

这思路的解法：

select t2.id,t2.visit_date,t2.people from

(select id,case when people>=100 then @count1 := @count1 + 1 else @count1 := 0 end as CNT

from stadium, (select @count1 := 0) as t) as t1,

(select id,visit_date,people,case when people>=100 then @count2 := @count2 + 1

else @count2 := 0

end as CNT

from stadium, (select @count2 := 0) as t) as t2

where t2.cnt >0 and (t1.id = t2.id+3-t2.CNT) and t1.CNT=3;

要把CNT=0的排除了，因为小于100；

 

至此，免费的sql题刷完了~

小有收获吧，但是还要回看的。不然收获很快就消失了~

加油吧，小菜鸡。