面试被虐的我 只能刷leetcode了
595、Big Countries(正常的查找)
select name, population, area from World where(population>25000000 or area>3000000);
627、Swap Salary(交换m和f)
update salary
set sex=
case sex
when ‘f’ then ‘m’
when ‘m’ then ‘f’
end;
看到一个case写的非常好的文章: https://www.cnblogs.com/aipan/p/7770611.html
620、Not Boring Movies(id为奇数,排列)
select * from cinema where (description!=‘boring’ and id%2=1) order by rating desc;
182、Duplicate Emails(输出重复行,select嵌套)
select Email from(select Email, count(Email) as count from Person group by Email) as haha where count>1;
也可以用group by having,详细参考: https://www.cnblogs.com/gqs92/archive/2017/04/26/6767973.html
175、Combine Two Tables(left join用法)
select a.Firstname, a.LastName, b.City, b.State from Person a left join Address b on a.PersonId=b.PersonId;
181、Employees Earning More Than Their Managers(inner join,select嵌套)
select Employee from(select a.Name as Employee,a.Salary,b.Name as mName, b.Salary as mSalary from Employee a inner join Employee b on a.ManagerId=b.Id)as haha where Salary>mSalary;
select a.Name as Employee from Employee as a inner join Employee as b on a.ManagerId=b.Id where a.Salary>b.Salary;
答案2:
SELECT
a.Name AS ‘Employee’
FROM
Employee AS a,
Employee AS b
WHERE
a.ManagerId = b.Id
AND a.Salary > b.Salary
;
183、Customers Who Never Order(两个表join,null判断)
select Customers from(select a.Name as Customers,b.Id as order_id from Customers a left join Orders b on a.Id=b.CustomerId)as haha where order_id is null;
select a.Name as Customers from Customers as a where a.Id not in (select CustomerId from Orders);
596、Classes More Than 5 Students(去重,分组,group by having)
select class from (
select class, count(class) as num from(
select * from courses group by class,student
) as heihei group by class
) as haha where num>=5;
select class from(select * from courses group by student,class)as haha group by class having count(class)>=5;
197、Rising Temperature