595. Big Countries
https://leetcode.com/problems/big-countries/description/
Description
+-----------------+------------+------------+--------------+---------------+ | 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 | +-----------------+------------+------------+--------------+---------------+
查找面积超过 3,000,000 或者人口数超过 25,000,000 的国家。
+--------------+-------------+--------------+ | name | population | area | +--------------+-------------+--------------+ | Afghanistan | 25500100 | 652230 | | Algeria | 37100000 | 2381741 | +--------------+-------------+--------------+
Solution
SELECT name, population, area FROM World WHERE area > 3000000 OR population > 25000000;
SQL Schema
SQL Schema 用于在本地环境下创建表结构并导入数据，从而方便在本地环境调试。
DROP TABLE IF EXISTS World; CREATE TABLE World ( NAME VARCHAR ( 255 ), continent VARCHAR ( 255 ), area INT, population INT, gdp INT ); INSERT INTO World ( NAME, continent, area, population, gdp ) VALUES ( 'Afghanistan', 'Asia', '652230', '25500100', '203430000' ), ( 'Albania', 'Europe', '28748', '2831741', '129600000' ), ( 'Algeria', 'Africa', '2381741', '37100000', '1886810000' ), ( 'Andorra', 'Europe', '468', '78115', '37120000' ), ( 'Angola', 'Africa', '1246700', '20609294', '1009900000' );
627. Swap Salary
https://leetcode.com/problems/swap-salary/description/
Description
| id | name | sex | salary | |----|------|-----|--------| | 1 | A | m | 2500 | | 2 | B | f | 1500 | | 3 | C | m | 5500 | | 4 | D | f | 500 |
只用一个 SQL 查询，将 sex 字段反转。
| id | name | sex | salary | |----|------|-----|--------| | 1 | A | f | 2500 | | 2 | B | m | 1500 | | 3 | C | f | 5500 | | 4 | D | m | 500 |
Solution
两个相等的数异或的结果为 0，而 0 与任何一个数异或的结果为这个数。
sex 字段只有两个取值：'f' 和 'm'，并且有以下规律：
'f' ^ ('m' ^ 'f') = 'm' ^ ('f' ^ 'f') = 'm' 'm' ^ ('m' ^ 'f') = 'f' ^ ('m' ^ 'm') = 'f'
因此将 sex 字段和 'm' ^ 'f' 进行异或操作，最后就能反转 sex 字段。
UPDATE salary SET sex = CHAR ( ASCII(sex) ^ ASCII( 'm' ) ^ ASCII( 'f' ) );
SQL Schema
DROP TABLE IF EXISTS salary; CREATE TABLE salary ( id INT, NAME VARCHAR ( 100 ), sex CHAR ( 1 ), salary INT ); INSERT INTO salary ( id, NAME, sex, salary ) VALUES ( '1', 'A', 'm', '2500' ), ( '2', 'B', 'f', '1500' ), ( '3', 'C', 'm', '5500' ), ( '4', 'D', 'f', '500' );
620. Not Boring Movies
https://leetcode.com/problems/not-boring-movies/description/
Description
+---------+-----------+--------------+-----------+ | 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 为奇数，并且 description 不是 boring 的电影，按 rating 降序。
+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+
Solution
SELECT * FROM cinema WHERE id % 2 = 1 AND description != 'boring' ORDER BY rating DESC;
SQL Schema
DROP TABLE IF EXISTS cinema; CREATE TABLE cinema ( id INT, movie VARCHAR ( 255 ), description VARCHAR ( 255 ), rating FLOAT ( 2, 1 ) ); INSERT INTO cinema ( id, movie, description, rating ) VALUES ( 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 );
596. Classes More Than 5 Students
https://leetcode.com/problems/classes-more-than-5-students/description/
Description
+---------+------------+ | student | class | +---------+------------+ | A | Math | | B | English | | C | Math | | D | Biology | | E | Math | | F | Computer | | G | Math | | H | Math | | I | Math | +---------+------------+
查找有五名及以上 student 的 class。
+---------+ | class | +---------+ | Math | +---------+
Solution
对 class 列进行分组之后，再使用 count 汇总函数统计每个分组的记录个数，之后使用 HAVING 进行筛选。HAVING 针对分组进行筛选，而 WHERE 针对每个记录（行）进行筛选。
SELECT class FROM courses GROUP BY class HAVING count( DISTINCT student ) >= 5;
SQL Schema
DROP TABLE IF EXISTS courses; CREATE TABLE courses ( student VARCHAR ( 255 ), class VARCHAR ( 255 ) ); INSERT INTO courses ( student, class ) VALUES ( 'A', 'Math' ), ( 'B', 'English' ), ( 'C', 'Math' ), ( 'D', 'Biology' ), ( 'E', 'Math' ), ( 'F', 'Computer' ), ( 'G', 'Math' ), ( 'H', 'Math' ), ( 'I', 'Math' );
182. Duplicate Emails
https://leetcode.com/problems/duplicate-emails/description/
Description
邮件地址表：
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
查找重复的邮件地址：
+---------+ | Email | +---------+ | a@b.com | +---------+
Solution
对 Email 进行分组，如果并使用 COUNT 进行计数统计，结果大于等于 2 的表示 Email 重复。
SELECT Email FROM Person GROUP BY Email HAVING COUNT( * ) >= 2;
SQL Schema
DROP TABLE IF EXISTS Person; CREATE TABLE Person ( Id INT, Email VARCHAR ( 255 ) ); INSERT INTO Person ( Id, Email ) VALUES ( 1, 'a@b.com' ), ( 2, 'c@d.com' ), ( 3, 'a@b.com' );
196. Delete Duplicate Emails
https://leetcode.com/problems/delete-duplicate-emails/description/
Description
邮件地址表：
+----+---------+ | Id | Email | +----+---------+ | 1 | john@example.com | | 2 | bob@example.com | | 3 | john@example.com | +----+---------+
删除重复的邮件地址：
+----+------------------+ | Id | Email | +----+------------------+ | 1 | john@example.com | | 2 | bob@example.com | +----+------------------+
Solution
只保留相同 Email 中 Id 最小的那一个，然后删除其它的。
连接查询：
DELETE p1 FROM Person p1, Person p2 WHERE p1.Email = p2.Email AND p1.Id > p2.Id
子查询：
DELETE FROM Person WHERE id NOT IN ( SELECT id FROM ( SELECT min( id ) AS id FROM Person GROUP BY email ) AS m );
应该注意的是上述解法额外嵌套了一个 SELECT 语句，如果不这么做，会出现错误：You can't specify target table 'Person' for update in FROM clause。以下演示了这种错误解法。
DELETE FROM Person WHERE id NOT IN ( SELECT min( id ) AS id FROM Person GROUP BY email );
参考：pMySQL Error 1093 - Can't specify target table for update in FROM clause(opens new window)
SQL Schema
与 182 相同。
