[LeetCode]595. Big Countries

发表于 | 更新于 | 分类于 | 评论数： | 阅读次数：
字数统计：361 字 | 阅读时长：2 分钟

595. Big Countries

今天这道题，是个sql语句的题目，选出符合条件的数据；

There is a table World

1
2
3
4
5
6
7
8
9
+-----------------+------------+------------+--------------+---------------+
| 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:

1
2
3
4
5
6
+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

比较简单，select where就可以解决了；

1
select name, population, area from World where area>3000000 or population>25000000;

result

Runtime: 196 ms, faster than 82.98% of MySQL online submissions for Big Countries.

结果有点惊讶，这么简单的还会有更好的解决办法？讨论区看了一眼，发现这么一个语句：

1
SELECT name, population, area FROM World WHERE area > 3000000 UNION SELECT name, population, area FROM World WHERE population > 25000000;

奇迹出现了？？

并没有。。。执行完效率并没有好多少，半斤八两。

这个时候就Google一番，or 和 union差在哪里，以下几篇文章有点参考意义。

  1. mysql 实战 or、in与union all 的查询效率
  2. SQL Performance UNION vs OR
  3. Whether to use UNION or OR in SQL Server Queries

总的来说，性能差异并不是绝对的，都需要针对实际数据进行实际的测试与验证，其中有一点是关键，对于非索引字段，or是肯定胜过union了。