SELECT IFNULL( (SELECTDISTINCT Salary FROM Employee ORDERBY Salary DESC LIMIT 1,1), NULL) AS SecondHighestSalary;
181. Employees Earning More Than Their Managers
分类:数据库
难度:简单
表的内连接加上条件即可
1 2 3 4 5 6
SELECT e1.Name AS Employee FROM Employee e1 JOIN Employee e2 ON e1.ManagerId = e2.Id AND e1.Salary > e2.Salary;
另一种解法,相对更快
1 2 3 4
SELECT* FROM Employee e1, Employee e2 WHERE e1.ManagerId = e2.Id AND e1.Salary > e2.Salary;
182. Duplicate Emails
分类:数据库
难度:简单
分组计数,满足条件的输出。
1 2 3 4
SELECT Email FROM Person GROUPBY Email HAVINGCOUNT(Email) >1;
183. Customers Who Never Order
分类:数据库
难度:简单
第一反应就是用连接保留 Customers 表的数据,再按题意将没有订单记录的用户输出。
表连接的结果如下:
Id
Name
Id
CustomerId
3
Sam
1
3
1
Joe
2
1
2
Henry
NULL
NULL
4
Max
NULL
NULL
1 2 3 4 5 6
SELECT Name AS Customers FROM Customers LEFTJOIN Orders ON Customers.Id = Orders.CustomerId WHERE Orders.Id ISNULL;
另一种相对更快的解法(表连接,真的慢)
1 2 3 4 5 6 7
SELECT Name AS Customers FROM Customers WHERE Customers.Id NOTIN( SELECTDISTINCT CustomerId FROM Orders );
196. Delete Duplicate Emails
分类:数据库
难度:简单
连接之后按条件删除。
1 2 3 4 5
DELETE p1 FROM Person p1 JOIN Person p2 WHERE p1.id > p2.id AND p1.Email = p2.Email;
另一种解法,相对更快一点。
1 2 3 4 5 6 7 8 9 10 11
DELETE FROM Person WHERE Id NOTIN( SELECT Id FROM( SELECTMIN(Id) AS Id FROM Person GROUPBY Email )AS tmp -- Every derived table must have its own alias );
197. Rising Temperature
分类:数据库
难度:简单
对 RecordDate 字段进行条件连接,然后比较 Temperature 的大小再输出 Id 即可。
1 2 3 4 5
SELECT w1.Id FROM Weather w1 JOIN Weather w2 ON w1.RecordDate = DATE_ADD(w2.RecordDate, INTERVAL1DAY) WHERE w1.Temperature > w2.Temperature; -- 也可以使用 AND
使用用 DATEDIFF 的解法,相对更快一点。
1 2 3 4 5
SELECT w1.Id FROM Weather w1 JOIN Weather w2 ON DATEDIFF(w1.RecordDate, w2.RecordDate) =1 WHERE w1.Temperature > w2.Temperature; -- 也可以使用 AND
595. Big Countries
分类:数据库
难度:简单
gdp 字段应该用 bigint 否则默认情况下 mysql 的 int 是存不了这些数据的。
满足条件输出即可
1 2 3 4
SELECT name, population, area FROM World WHERE area >3000000 OR population >25000000;
或者使用 UNION 合并两个查询结果再输出
1 2 3 4 5 6 7
SELECT name, population, area FROM World WHERE area >3000000 UNION SELECT name, population, area FROM World WHERE population >25000000;
596. Classes More Than 5 Students
分类:数据库
难度:简单
分组计数输出满足条件的课程名称
1 2 3 4
SELECT class FROM courses GROUPBY class HAVINGCOUNT(DISTINCT student) >=5;
或者先建立临时表储存 课程-人数 ,然后再输出参与人数大于等于 5 的课程,执行速度相对来说更快。
1 2 3 4 5 6 7
SELECT class FROM( SELECT class, COUNT(DISTINCT student) AS num FROM courses GROUPBY class ) AS tmp WHERE num >=5;
620. Not Boring Movies
分类:数据库
难度:简单
选择 id 为奇数,description 不是 boring 的数据,并将其降序排列。
1 2 3 4 5
SELECT* FROM cinema WHERE (id %2) >0 AND description NOTLIKE'%boring%' ORDERBY rating DESC;
627. Swap Salary
分类:数据库
难度:简单
要求不要使用查询语句
1 2 3 4 5
UPDATE salary SET sex =CASE WHEN sex ='m'THEN'f' WHEN sex ='f'THEN'm' ELSE sex END;
也可以简化为一条分支语句
1 2 3 4 5 6 7 8
UPDATE salary SET sex =CASE WHEN sex ='m'THEN'f' ELSE'm'END;
-- 使用 IF 的方法 UPDATE salary SET sex = IF(sex='f', 'm', 'f');
1179. Reformat Department Table
分类:数据库
难度:简单
按 id 分组,把 12 个月的收入按月求和输出,并重命名。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SELECT id, SUM(IF(month='Jan', revenue, NULL)) AS Jan_Revenue, SUM(IF(month='Feb', revenue, NULL)) AS Feb_Revenue, SUM(IF(month='Mar', revenue, NULL)) AS Mar_Revenue, SUM(IF(month='Apr', revenue, NULL)) AS Apr_Revenue, SUM(IF(month='May', revenue, NULL)) AS May_Revenue, SUM(IF(month='Jun', revenue, NULL)) AS Jun_Revenue, SUM(IF(month='Jul', revenue, NULL)) AS Jul_Revenue, SUM(IF(month='Aug', revenue, NULL)) AS Aug_Revenue, SUM(IF(month='Sep', revenue, NULL)) AS Sep_Revenue, SUM(IF(month='Oct', revenue, NULL)) AS Oct_Revenue, SUM(IF(month='Nov', revenue, NULL)) AS Nov_Revenue, SUM(IF(month='Dec', revenue, NULL)) AS Dec_Revenue FROM Department GROUPBY id;
也可以使用 CASE 语句。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
SELECT id, SUM(CASEmonthWHEN'Jan'THEN revenue END) AS Jan_Revenue, SUM(CASEmonthWHEN'Feb'THEN revenue END) AS Feb_Revenue, SUM(CASEmonthWHEN'Mar'THEN revenue END) AS Mar_Revenue, SUM(CASEmonthWHEN'Apr'THEN revenue END) AS Apr_Revenue, SUM(CASEmonthWHEN'May'THEN revenue END) AS May_Revenue, SUM(CASEmonthWHEN'Jun'THEN revenue END) AS Jun_Revenue, SUM(CASEmonthWHEN'Jul'THEN revenue END) AS Jul_Revenue, SUM(CASEmonthWHEN'Aug'THEN revenue END) AS Aug_Revenue, SUM(CASEmonthWHEN'Sep'THEN revenue END) AS Sep_Revenue, SUM(CASEmonthWHEN'Oct'THEN revenue END) AS Oct_Revenue, SUM(CASEmonthWHEN'Nov'THEN revenue END) AS Nov_Revenue, SUM(CASEmonthWHEN'Dec'THEN revenue END) AS Dec_Revenue FROM Department GROUPBY id;