SQL查询航班数据时如何正确排序到达与出发时间？

Let's work through this sorting issue together. The core problem here is that your current query mixes two types of flights: those arriving on $date, and those departing on $date but arriving the next day. Your current ORDER BY eta ASC only sorts by arrival time, which doesn't account for the fact that you want same-day arrivals to come before same-day departures—even if the departure time is earlier than the arrival time.

The Solution: Use a Two-Level Sort with CASE Statements #

We need to split the sorting into two logical parts:

1. First, group flights so all same-day arrivals (arrdate = '$date') come before same-day departures that arrive the next day (depdate = '$date' AND arrdate != '$date').
2. Then, sort within each group: same-day arrivals by their arrival time (eta), and same-day departures by their departure time.

Here's the adjusted SQL query:

SELECT * 
FROM flights 
WHERE arrdate = '$date' OR depdate = '$date'
ORDER BY 
    -- First, prioritize same-day arrivals over same-day departures (next-day arrivals)
    CASE WHEN arrdate = '$date' THEN 1 ELSE 2 END ASC,
    -- Then sort within each group: arrivals by eta, departures by departure time
    CASE 
        WHEN arrdate = '$date' THEN eta 
        ELSE dep_time  -- Replace with your actual departure time column name if different
    END ASC;

How This Works #

• The first CASE statement assigns a priority value: 1 to same-day arrivals, 2 to same-day departures. Since we're sorting in ascending order, all group 1 flights will appear before group 2.
• The second CASE statement handles intra-group sorting: same-day arrivals are ordered by their arrival time (eta), while same-day departures are ordered by their departure time (tweak dep_time to match your actual column name if needed).

Quick Security Note #

Just a heads-up: directly inserting variables like $date into your SQL string exposes you to SQL injection attacks. Instead, use parameterized queries (e.g., prepared statements in PDO or MySQLi if you're using PHP) to safely pass values into your query.

内容的提问来源于stack exchange，提问作者Frenzye