复杂的SQL查询，需要根据时间限制通过计数排序
大家好，我这里有以下三张表。
COUNTRIES
ID | Name | Details
Airports
ID | NAME | CountryID
Trips
ID | AirportID | Date
我必须检索一个显示以下内容的列表：
AirportID | AIrport Name | Country Name | Number of Trips Made Between Date1 and Date2
我需要它真正高效，我需要设置什么样的索引以及如何在此处制定 SQL 查询？我将使用 Php 来显示它。请注意，我需要能够根据行程次数进行排序。
编辑==
哎呀忘了提及我的sql：
我尝试了以下方法：
SELECT `c`.*, `t`.`country` AS `country_name`, COUNT(f.`id`) AS `num_trips` FROM `airports` AS `c`
LEFT JOIN `countries` AS `t` ON t.`id` = c.`country_id`
LEFT JOIN `trips` AS `f` ON f.`airportid` = c.`id` GROUP BY `c`.`id` ORDER BY `num_flights` ASC LIMIT 10
它可以工作，但执行起来需要很长的时间 - 再加上考虑到我的机场表有超过30'000个条目，并且旅行表是可变的。
我只是从国家表中获取国家/地区名称 - 如果我在 sql 中排除加入国家/地区表，而是从数组中检索国家/地区名称，其中索引是 ID，值是，会更好吗？国家名称？
Hi guys I have the following three tables here.
COUNTRIES
ID | Name | Details
Airports
ID | NAME | CountryID
Trips
ID | AirportID | Date
I have to retrieve a list showing the following:
AirportID | AIrport Name | Country Name | Number of Trips Made Between Date1 and Date2
I need this to be really efficient, what kind of indexes do I need to set up and how would I formulate the SQL query here? I would be displaying this using Php. Note that I need to be able to sort based upon the number of trips made.
EDIT ==
Oops forgot to mention my sql:
I've tried the following:
SELECT `c`.*, `t`.`country` AS `country_name`, COUNT(f.`id`) AS `num_trips` FROM `airports` AS `c`
LEFT JOIN `countries` AS `t` ON t.`id` = c.`country_id`
LEFT JOIN `trips` AS `f` ON f.`airportid` = c.`id` GROUP BY `c`.`id` ORDER BY `num_flights` ASC LIMIT 10
It works but takes a really looong time to execute - plus consider this that my airports table has over 30'000 entries and teh trips table is variable.
I'm just taking the name of the country from the countries table - would it be better if I were to instead exclude joining teh countries table in the sql and instead retrieve the country name from an array where the index is the ID and values are the names of countries?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我不确定你为什么使用左连接。如果每次旅行都有一个机场并且每个机场都有一个国家/地区，则内部联接将为您提供准确的结果。
我会这样做：
<代码>
选择 a.ID 作为 AirportID，a.Name 作为 AirportName，c.Name 作为 CountryName，count(t.id) 作为 NumTrips
来自 旅行 t
内连接 t.AirportID = a.ID 上的机场 a
在 a.CountryID = c.ID 上内连接国家 c
其中 t.Date >= @StartDate
且 t.Date <= @EndDate
按 AirportID、机场名称、国家/地区名称分组
按 NumTrips 排序
限制 10
将 @StartDate 和 @EndDate 替换为适当的值。
不确定您想要的结果是什么，但我预计您想要最多的行程。在这种情况下，您需要执行“order by NumTrips desc”。这将首先显示最高值，特别是因为您将其限制为 10。
另外，我建议您将“日期”列重命名为不会与保留 SQL 字冲突的名称。我通常使用“DateCreated”或“DateOfTravel”或类似的东西。
如果我做出了任何错误的假设，请告诉我，我可以重写此内容。
编辑：
对于索引，在您要查找的字段上创建它们。换句话说，主键（应该始终被索引）、外键，在这种情况下，日期列似乎是另一个重要的索引。但是，如果您打算按“机场名称”进行搜索，请在其中添加索引。我想你知道这会走向何方等等。
I'm not sure why you're using left joins. If every trip has an airport and every airport has a country, and inner join would give you accurate results.
I would do this:
select a.ID as AirportID, a.Name as AirportName, c.Name as CountryName, count(t.id) as NumTrips
from Trips t
inner join Airports a on t.AirportID = a.ID
inner join Countries c on a.CountryID = c.ID
where t.Date >= @StartDate
and t.Date <= @EndDate
group by AirportID, AirportName, CountryName
order by NumTrips
limit 10
Replace the @StartDate and @EndDate with your appropriate values.
Not sure what you're looking for in results, but I would expect you want the most trips. In that case you would want to do "order by NumTrips desc". This will show the highest values first, especially since you're limiting it to 10.
Also, I suggest you rename your "Date" column to something that won't collide with reserved SQL words. I usually use "DateCreated" or "DateOfTravel" or something like that.
If I made any poor assumptions let me know and I can re-write this.
Edit:
For indexes, create them on fields you will be looking up on. In other words, primary keys (which should always be indexed), foreign keys, and in this case it looks like the Date column would be the other important index. However, if you plan on searching by "Airport Name", then add an index there. I think you see where this is headed, etc.
airpoirt(countryid, id)和trips(airportid)上的索引似乎是最重要的。尝试使用
count(f.airportid)而不是count(f.id)，这样 MySQL 就不必检查trips.id列。Indexes on
airpoirt(countryid, id)andtrips(airportid)would seem the most important.Instead of
count(f.id)trycount(f.airportid), so MySQL doesn't have to check thetrips.idcolumn.