mysql select选择多列_如何在MySQL中通過多列主鍵選擇多行？

I have a table with a multi-column primary key (city/state/date) and many more columns of data. I'm looking to get the latest data for each city/state. How do I do that cleanly/efficiently? Right now I can do this by doing a first query to get the list of all the rows I'm trying to fetch, followed by a second query with a massive WHERE clause:

我有一個包含多列主鍵(城市/州/日期)和更多數據列的表。我希望得到每個城市/州的最新數據。如何干凈/高效地完成這項工作?現在我可以通過執行第一個查詢來獲取我正在嘗試獲取的所有行的列表,然后使用大量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

...

This is really ugly and inefficient, and if the first query returns a lot of rows my second query might be too long. Clearly if I have a single-column primary key I could use a subselect with IN(), but that's not really possible here. Any suggestions?

這真的很丑陋且效率低下,如果第一個查詢返回很多行,我的第二個查詢可能會太長。顯然,如果我有一個單列主鍵,我可以使用帶有IN()的子選擇,但這在這里是不可能的。有什么建議么?

UPDATE: I tried Bill's suggestion with a subselect, but it's not using any keys and is taking forever. If I restrict the subselect to only return 5 rows it returns in 0.64s. If I let it return all 73 city/state combinations, it takes a very long time (query still running).

更新:我用一個subselect嘗試了Bill的建議,但它沒有使用任何鍵,而是永遠。如果我將subselect限制為僅返回5行,則返回0.64s。如果我讓它返回所有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 |

+----+--------------------+-------+-------+---------------+---------+---------+------+-------+-------------+

2 个解决方案

#1

4

I think this should do the trick for you:

我認為這應該適合你:

select

*

from

data t1

natural join

(

select

city,

state,

max(date) as date

from

data

group by

city,

state

) t2;

#2

4

MySQL supports tuple comparisons:

MySQL支持元組比較:

SELECT * FROM data WHERE

(state, city, date) IN (

('CA', 'San Francisco', '2013-09-01'),

('CA', 'Los Angeles', '2013-08-01'),

('NY', 'New York', '2013-10-01'));