我有一个带有多列主键(城市/州/日期)和更多数据列的表。我正在寻找每个城市/州的最新数据。我如何干净/高效地做到这一点?现在,我可以通过执行第一个查询来获取我尝试获取的所有行的列表,然后执行带有大量 WHERE 子句的第二个查询:
SELECT state, city, max(date) from data GROUP BY city, state;
+-------+---------------------+------------+
| state | city | MAX(date) |
+-------+---------------------+------------+
| CA | San Francisco | 2013-09-01 |
| CA | Los Angeles | 2013-08-01 |
| NY | New York | 2013-10-01 |
| ... | ... (many rows) ... | ... |
+-------+---------------------+------------+
SELECT * FROM data WHERE
(state = "CA" AND city = "San Francisco" AND date='2013-09-01') OR
(state = "CA" AND city = "Los Angeles" AND date='2013-08-01') OR
(state = "NY" AND city = "New York" AND date='2013-10-01') OR
...
这确实很难看且效率低下,如果第一个查询返回很多行,我的第二个查询可能会太长。显然,如果我有一个单列主键,我可以使用 IN() 进行子选择,但这在这里实际上是不可能的。有什么建议么?
更新:我用子选择尝试了比尔的建议,但它没有使用任何键并且需要很长时间。如果我将子选择限制为仅返回 5 行,它将在 0.64 秒内返回。如果我让它返回所有 73 个城市/州组合,则需要很长时间(查询仍在运行)。
EXPLAIN SELECT * FROM data WHERE (city, state, date) IN (SELECT state, city, MAX(date) FROM data GROUP BY city, state)
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
| 1 | PRIMARY | data | ALL | NULL | NULL | NULL | NULL | 13342 | Using where |
| 2 | DEPENDENT SUBQUERY | data | index | NULL | PRIMARY | 57 | NULL | 8058 | Using index |
+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+
我认为这应该对你有用:
select
*
from
data t1
natural join
(
select
city,
state,
max(date) as date
from
data
group by
city,
state
) t2;
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)