1.leetcode-182题:
写一个可以查询重复email的sql。
Write a SQL query to find all duplicate emails in a table named Person
.
+----+---------+ | Id | Email | +----+---------+ | 1 | a@b.com | | 2 | c@d.com | | 3 | a@b.com | +----+---------+
For example, your query should return the following for the above table:
+---------+ | Email | +---------+ | a@b.com | +---------+
解题思路:
首先通过group by通过Email进行分组,并获得每个Email出现的次数。
select Email,count(Email) as num from Person group by Email
将上面的查询结果作为一个子查询,再从中查询数量大于1的Email,即为重复的Email。
完整sql如下:
select Email from
# 子查询,使用group by根据Email进行分组
(select Email,count(Email) as num from Person group by Email) as nums
# 根据Email分组后数量超过1的为重复的Email
where num >1
2.leetcode-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 | +--------------+-------------+--------------+
英文:
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 | +--------------+-------------+--------------+
解题思路:
这题很简单,查询条件为where area> 3000000 OR population >25000000; 就ok了。
完整sql如下:
SELECT name,population,area from World
where area> 3000000 OR population >25000000;
3.leetcode-620题——Not Boring Movies:
中文:
X市开了一家新电影院,很多人都想去这家电影院。电影院还会发布一张海报,说明电影的评级和描述。
请编写一个SQL查询来输出具有奇数编号ID和描述不是“无聊”的电影。按rating进行排序。
例如,table电影:
+---------+-----------+--------------+-----------+ | 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 | +---------+-----------+--------------+-----------+
英文:
X city opened a new cinema, many people would like to go to this cinema. The cinema also gives out a poster indicating the movies’ ratings and descriptions.
Please write a SQL query to output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating.
For example, table 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 | +---------+-----------+--------------+-----------+
For the example above, the output should be:
+---------+-----------+--------------+-----------+ | id | movie | description | rating | +---------+-----------+--------------+-----------+ | 5 | House card| Interesting| 9.1 | | 1 | War | great 3D | 8.9 | +---------+-----------+--------------+-----------+
解题思路:
主要考察的是where条件部分,一共三点:
(1)编号为单数;
(2)描述description不为boring;
(3)并按照rating 从大到小排序
完整sql:
SELECT * FROM cinema
# 为单数的编号
WHERE id % 2 != 0
# 描述不为boring的
AND description!='boring'
# 并按照rating 从大到小排序
ORDER BY rating DESC;