网上写写题 提高下自己的能力。
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题刷完了~
小有收获吧,但是还要回看的。不然收获很快就消失了~
加油吧,小菜鸡。