如何创建多列主键mysql_如何在MySQL中通过多列主键选择多行？

最新推荐文章于 2021-03-17 07:48:55 发布

weixin_39708636

最新推荐文章于 2021-03-17 07:48:55 发布

阅读量188

点赞数

文章标签：如何创建多列主键mysql

本文链接：https://blog.csdn.net/weixin_39708636/article/details/113714059

版权

我有一个包含多列主键(城市/州/日期)和更多数据列的表.我希望得到每个城市/州的最新数据.如何干净/高效地完成这项工作？现在我可以通过执行第一个查询来获取我正在尝试获取的所有行的列表,然后使用大量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()的子选择,但这在这里是不可能的.有什么建议？

更新：我用一个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)

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

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

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

关注