单表查询
Rising Temperature(Easy)
Given a Weather table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
For example, return the following Ids for the above Weather table:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
Solution:
MySQL DATE_SUB()，DATE_SUB(date,INTERVAL expr type) 从日期减去指定的时间间隔。
减去 2 天：DATE_SUB(OrderDate,INTERVAL 2 DAY)
SELECT w1.id FROM weather AS w1
LEFT JOIN weather AS w2
ON DATE_SUB(w1.recordDate, INTERVAL 1 Day) = w2.recordDate
WHERE w1.temperature > w2.temperature;
DATEDIFF(expr1,expr2) 返回俩个日期的天数间隔
SELECT
weather.id AS 'Id'
FROM
weather
JOIN
weather w ON DATEDIFF(weather.date, w.date) = 1
AND weather.Temperature > w.Temperature
;
Big Coutries(Easy)
There is a table World
+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+
A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.
Write a SQL solution to output big countries' name, population and area.
For example, according to the above table, we should output:
+--------------+-------------+--------------+
| name | population | area |
+--------------+-------------+--------------+
| Afghanistan | 25500100 | 652230 |
| Algeria | 37100000 | 2381741 |
+--------------+-------------+--------------+
Solution:
SELECT name, population, area
FROM World
WHERE population > 25000000 OR area > 3000000
使用 or 会使索引会失效，在数据量较大的时候查找效率较低，通常建议使用 union 代替 or。
SELECT
name, population, area
FROM
world
WHERE
area > 3000000
UNION
SELECT
name, population, area
FROM
world
WHERE
population > 25000000
;
Reformat Department Table(Easy)
Table: Department,(id, month) is the primary key of this table.The table has information about the revenue of each department per month.The month has values in ["Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"].
+---------------+---------+
| Column Name | Type |
+---------------+---------+
| id | int |
| revenue | int |
| month | varchar |
+---------------+---------+
Write an SQL query to reformat the table such that there is a department id column and a revenue column for each month.
The query result format is in the following example:
Department table:
+------+---------+-------+
| id | revenue | month |
+------+---------+-------+
| 1 | 8000 | Jan |
| 2 | 9000 | Jan |
| 3 | 10000 | Feb |
| 1 | 7000 | Feb |
| 1 | 6000 | Mar |
+------+---------+-------+
Result table:
+------+-------------+-------------+-------------+-----+-------------+
| id | Jan_Revenue | Feb_Revenue | Mar_Revenue | ... | Dec_Revenue |
+------+-------------+-------------+-------------+-----+-------------+
| 1 | 8000 | 7000 | 6000 | ... | null |
| 2 | 9000 | null | null | ... | null |
| 3 | null | 10000 | null | ... | null |
+------+-------------+-------------+-------------+-----+-------------+
Note that the result table has 13 columns (1 for the department id + 12 for the months).
Solution:
CASE field WHEN value THEN result END, field 筛选字段，value 特定值，result 返回值，转换符合条件的字段。
SELECT id,
SUM(CASE month WHEN 'Jan' THEN revenue END) as Jan_Revenue,
SUM(CASE month WHEN 'Feb' THEN revenue END) as Feb_Revenue,
SUM(CASE month WHEN 'Mar' THEN revenue END) as Mar_Revenue,
SUM(CASE month WHEN 'Apr' THEN revenue END) as Apr_Revenue,
SUM(CASE month WHEN 'May' THEN revenue END) as May_Revenue,
SUM(CASE month WHEN 'Jun' THEN revenue END) as Jun_Revenue,
SUM(CASE month WHEN 'Jul' THEN revenue END) as Jul_Revenue,
SUM(CASE month WHEN 'Aug' THEN revenue END) as Aug_Revenue,
SUM(CASE month WHEN 'Sep' THEN revenue END) as Sep_Revenue,
SUM(CASE month WHEN 'Oct' THEN revenue END) as Oct_Revenue,
SUM(CASE month WHEN 'Nov' THEN revenue END) as Nov_Revenue,
SUM(CASE month WHEN 'Dec' THEN revenue END) as Dec_Revenue
FROM Department
GROUP BY id;
Delete Duplicate Emails(Easy)
Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
Solution:
将数据表自己连接。
DELETE p1
FROM person AS p1,
person AS p2
WHERE p1.email = p2.email AND p1.id > p2.id;
Classes More Than 5 Students
There is a table courses with columns: student and class
Please list out all classes which have more than or equal to 5 students.
For example, the table:
+---------+------------+
| student | class |
+---------+------------+
| A | Math |
| B | English |
| C | Math |
| D | Biology |
| E | Math |
| F | Computer |
| G | Math |
| H | Math |
| I | Math |
+---------+------------+
Should output:
+---------+
| class |
+---------+
| Math |
+---------+
Note: The students should not be counted duplicate in each course.
Solution:
在 SQL 中增加
HAVING子句原因是，WHERE关键字无法与聚合函数一起使用。HAVING 子句可以让我们筛选分组后的各组数据。
SELECT class FROM courses
GROUP BY class
HAVING COUNT(DISTINCT student) >= 5;
双表查询
The Employee table holds all employees. Every employee has an Id, a salary, and there is also a column for the department Id.
+----+-------+--------+--------------+
| Id | Name | Salary | DepartmentId |
+----+-------+--------+--------------+
| 1 | Joe | 70000 | 1 |
| 2 | Jim | 90000 | 1 |
| 3 | Henry | 80000 | 2 |
| 4 | Sam | 60000 | 2 |
| 5 | Max | 90000 | 1 |
+----+-------+--------+--------------+
The Department table holds all departments of the company.
+----+----------+
| Id | Name |
+----+----------+
| 1 | IT |
| 2 | Sales |
+----+----------+
Department Highest Salary(Middle)
Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, your SQL query should return the following rows (order of rows does not matter).
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Jim | 90000 |
| Sales | Henry | 80000 |
+------------+----------+--------+
Explanation:
Max and Jim both have the highest salary in the IT department and Henry has the highest salary in the Sales department.
Solution:
SELECT
Department.name AS 'Department',
Employee.name AS 'Employee',
Salary
FROM
Employee
JOIN
Department ON Employee.DepartmentId = Department.Id
WHERE
(Employee.DepartmentId , Salary) IN
( SELECT
DepartmentId, MAX(Salary)
FROM
Employee
GROUP BY DepartmentId
)
;
Exchange Seats(Middle)
Mary is a teacher in a middle school and she has a table seat storing students' names and their corresponding seat ids.The column id is continuous increment.Mary wants to change seats for the adjacent students.Can you write a SQL query to output the result for Mary?
+---------+---------+
| id | student |
+---------+---------+
| 1 | Abbot |
| 2 | Doris |
| 3 | Emerson |
| 4 | Green |
| 5 | Jeames |
+---------+---------+
For the sample input, the output is:
+---------+---------+
| id | student |
+---------+---------+
| 1 | Doris |
| 2 | Abbot |
| 3 | Green |
| 4 | Emerson |
| 5 | Jeames |
+---------+---------+
Note: If the number of students is odd, there is no need to change the last one's seat.
Solution:
SELECT
(CASE
-- 转换，奇数 -> 偶数
WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
-- 最后一位奇数，不变
WHEN MOD(id, 2) != 0 AND counts = id THEN id
-- 偶数 - > 奇数
ELSE id - 1
END) AS id,
student
FROM
-- 获取学生总数
seat,
(SELECT
COUNT(*) AS counts
FROM
seat) AS seat_counts
ORDER BY id ASC;
Department Top Three Salaries(Difficult)
Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows (order of rows does not matter).
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Joe | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
Explanation：
In IT department, Max earns the highest salary, both Randy and Joe earn the second highest salary, and Will earns the third highest salary. There are only two employees in the Sales department, Henry earns the highest salary while Sam earns the second highest salary.
Solution：
题意：部门工资前三高 => 当前薪水排名 ≤ 3
SELECT
d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
Employee e1
JOIN
Department d ON e1.DepartmentId = d.Id
WHERE
3 > (SELECT
COUNT(DISTINCT e2.Salary)
FROM
Employee e2
WHERE
e2.Salary > e1.Salary
AND e1.DepartmentId = e2.DepartmentId
)
;
评论