182. 查找重复的电子邮箱
编写一个 SQL 查询,查找 Person
表中所有重复的电子邮箱。
示例:
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
根据以上输入,你的查询应返回以下结果:
+---------+ | Email | +---------+ | a@b.com | +---------+
#oj1:先按照Email进行分组,然后通过HAVING语句筛选出每个组Email个数大于1的;
SELECT Email FROM Person GROUP BY Email HAVING COUNT(Email)>1;
#oj2:使用INNER JOIN 拼接Person表中具有相同Email,并且Id不相同的记录(同一个Id有相同的Emial),然后使用DISTINCT将重复的记录去掉(Id:1和Id:3满足,Id:3和Id:1也满足,所有需要使用DISTINCT去掉重复);
SELECT DISTINCT p1.Email FROM Person p1 INNER JOIN Person p2 ON p1.Email=p2.Email WHERE p1.Id<>p2.Id;
595. 大的国家
这里有张 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 | +-----------------+------------+------------+--------------+---------------+
如果一个国家的面积超过300万平方公里,或者人口超过2500万,那么这个国家就是大国家。
编写一个SQL查询,输出表中所有大国家的名称、人口和地区。
例如,根据上表,我们应该输出:
+--------------+-------------+--------------+ | name | population | area | +--------------+-------------+--------------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +--------------+-------------+--------------+
直接写出结果:
SELECT name, population, area FROM World WHERE area > 3000000 OR population > 25000000;
620. 有趣的电影
作为该电影院的信息部主管,您需要编写一个 SQL查询,找出所有影片描述为非 boring
(不无聊) 的并且 id 为奇数 的影片,结果请按等级 rating
排列。
例如,下表 cinema
:
+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 1 | War | great 3D | 8.9 | | 2 | Science | fiction | 8.5 | | 3 | irish | boring | 6.2 | | 4 | Ice song | Fantacy | 8.6 | | 5 | House card| Interesting| 9.1 | +---------+-----------+--------------+-----------+
对于上面的例子,则正确的输出是为:
+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+
直接写出结果:
SELECT id,movie,description,rating FROM cinema WHERE (id%2 <> 0 AND description <> "boring") ORDER BY rating DESC;
627. 交换工资
给定一个 salary
表,如下所示,有m=男性 和 f=女性的值 。交换所有的 f 和 m 值(例如,将所有 f 值更改为 m,反之亦然)。要求使用一个更新查询,并且没有中间临时表。
例如:
| id | name | sex | salary | |----|------|-----|--------| | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 |
运行你所编写的查询语句之后,将会得到以下表:
| id | name | sex | salary | |----|------|-----|--------| | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 |
#oj1:使用IF语句进行解决;
UPDATE salary SET sex = IF(sex='m','f','m');
#oj2:使用CASE语句来解决;
UPDATE salary SET sex=(
CASE sex
WHEN 'm' THEN 'f'
ELSE 'm'
END
);
175. 组合两个
表1: Person
+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | PersonId | int | | FirstName | varchar | | LastName | varchar | +-------------+---------+ PersonId 是上表主键
表2: Address
+-------------+---------+ | 列名 | 类型 | +-------------+---------+ | AddressId | int | | PersonId | int | | City | varchar | | State | varchar | +-------------+---------+ AddressId 是上表主键
编写一个 SQL 查询,满足条件:无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息:
FirstName, LastName, City, State
#oj1:题目要求无论 person 是否有地址信息,都需要基于上述两表提供 person 的以下信息,使用LEFT JOIN,将LEFT左边的表作为主表,无论Address表是否为空,都要把Person表数据检索出来;
SELECT p.FirstName,p.LastName,a.City,a.State FROM Person p LEFT JOIN Address a ON p.PersonId=a.PersonId;
举例:
181. 超过经理收入的员工
Employee
表包含所有员工,他们的经理也属于员工。每个员工都有一个 Id,此外还有一列对应员工的经理的 Id。
+----+-------+--------+-----------+ | Id | Name | Salary | ManagerId | +----+-------+--------+-----------+ | 1 | Joe | 70000 | 3 | | 2 | Henry | 80000 | 4 | | 3 | Sam | 60000 | NULL | | 4 | Max | 90000 | NULL | +----+-------+--------+-----------+
给定 Employee
表,编写一个 SQL 查询,该查询可以获取收入超过他们经理的员工的姓名。在上面的表格中,Joe 是唯一一个收入超过他的经理的员工。
+----------+ | Employee | +----------+ | Joe | +----------+
使用自联结(联结的是同一个表):
SELECT e.Name AS Employee FROM Employee e,Employee m WHERE e.ManagerId = m.Id AND e.Salary > m.Salary;
183. 从不订购的客户
某网站包含两个表,Customers
表和 Orders
表。编写一个 SQL 查询,找出所有从不订购任何东西的客户。
Customers
表:
+----+-------+ | Id | Name | +----+-------+ | 1 | Joe | | 2 | Henry | | 3 | Sam | | 4 | Max | +----+-------+
Orders
表:
+----+------------+ | Id | CustomerId | +----+------------+ | 1 | 3 | | 2 | 1 | +----+------------+
例如给定上述表格,你的查询应返回:
+-----------+ | Customers | +-----------+ | Henry | | Max | +-----------+
先查出Orders表中的CustomerId,然后不在CustomerId中的就是从不订购任何东西的客户,使用NOT IN解决:
SELECT Name AS Customers FROM Customers WHERE Id NOT IN (SELECT CustomerId FROM Orders);
176. 第二高的薪水
编写一个 SQL 查询,获取 Employee
表中第二高的薪水(Salary) 。
+----+--------+ | Id | Salary | +----+--------+ | 1 | 100 | | 2 | 200 | | 3 | 300 | +----+--------+
例如上述 Employee
表,SQL查询应该返回 200
作为第二高的薪水。如果不存在第二高的薪水,那么查询应返回 null
。
+---------------------+ | SecondHighestSalary | +---------------------+ | 200 | +---------------------+
oj1:使用子查询,先查出最大Salary,然后再查出小于最大Salary,然后最大的Salary(第二高),此种方法拓展性较差:
SELECT MAX(Salary) AS SecondHighestSalary FROM Employee WHERE Salary < (SELECT MAX(Salary) FROM Employee);
oj2:先通过ORDER BY对所有Salary进行排序,然后通过DISTINCT去掉重复的Salary,最后通过LIMIT(LIMIT 5,10; // 检索记录行 6-15)选择第N高的Salary:
SELECT DISTINCT Salary AS SecondHighestSalary FROM Employee ORDER BY Salary DESC LIMIT 1,1;
596. 超过5名学生的课
有一个courses
表 ,有: student (学生) 和 class (课程)。
请列出所有超过或等于5名学生的课。
例如,表:
+---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+
应该输出:
+---------+ | class | +---------+ | Math | +---------+
Note:
学生在每个课中不应被重复计算。
备注:题目要求“学生在每个课中不应被重复计算”,所以需要注意同一个学生选择了多门相同的课,采用DISTINCT先将完全相同的记录去掉,然后在利用GROUP BY将class分组,列出所有超过或等于5名学生的课。
SELECT class FROM (SELECT DISTINCT * FROM courses) a GROUP BY class HAVING COUNT(class)>=5;
196. 删除重复的电子邮箱
编写一个 SQL 查询,来删除 Person
表中所有重复的电子邮箱,重复的邮箱里只保留 Id 最小 的那个。
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+------------------+ Id 是这个表的主键。
例如,在运行你的查询语句之后,上面的 Person
表应返回以下几行:
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
备注:使用自联结。
DELETE p1 FROM Person p1,Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id;
197. 上升的温度
给定一个 Weather
表,编写一个 SQL 查询,来查找与之前(昨天的)日期相比温度更高的所有日期的 Id。
+---------+------------------+------------------+ | 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 | +---------+------------------+------------------+
例如,根据上述给定的 Weather
表格,返回如下 Id:
+----+ | Id | +----+ | 2 | | 4 | +----+
备注:使用自联结。
SELECT a.Id FROM Weather a,Weather b WHERE TO_DAYS(a.RecordDate)-TO_DAYS(b.RecordDate)=1 AND a.Temperature > b.Temperature;