如何用SQL实现行程按每日排序并展示？技术咨询

Alright, let's tackle this problem. The goal is to expand itinerary entries that span multiple days (like the Ibis Hotel staying from 2019-10-01 to 2019-10-10) into individual daily rows, then sort everything by date first, and by the original Order field for entries on the same day.

Core Logic #

We need two key steps to make this work:

1. Generate a continuous sequence of dates covering the entire range of your itinerary (from the earliest DateFrom to the latest DateTo).
2. Join this date sequence with your ItineraryTable to match each date with all entries active on that day (i.e., entries where the date falls between DateFrom and DateTo).
3. Sort the results by date ascending, then by the original Order ascending to keep daily entries in your intended order.

Database-Specific SQL Examples #

MySQL 8.0+ (Using Recursive CTE)

MySQL doesn't have a built-in date series generator, so we'll use a recursive CTE to create our date range:

WITH RECURSIVE date_range AS (
    -- Start with the earliest and latest dates from your itinerary
    SELECT MIN(DateFrom) AS day_date, MAX(DateTo) AS max_date
    FROM ItineraryTable
    UNION ALL
    -- Add one day at a time until we reach the latest date
    SELECT DATE_ADD(day_date, INTERVAL 1 DAY), max_date
    FROM date_range
    WHERE day_date < max_date
)
SELECT 
    dr.day_date,
    it.ID,
    it.Title,
    it.DateFrom,
    it.DateTo,
    it.Order
FROM date_range dr
JOIN ItineraryTable it 
    ON dr.day_date BETWEEN it.DateFrom AND it.DateTo
ORDER BY dr.day_date ASC, it.Order ASC;

PostgreSQL (Using generate_series)

PostgreSQL makes date range generation straightforward with the generate_series function:

SELECT 
    gs.day_date::DATE,
    it.ID,
    it.Title,
    it.DateFrom,
    it.DateTo,
    it.Order
FROM generate_series(
    (SELECT MIN(DateFrom) FROM ItineraryTable),
    (SELECT MAX(DateTo) FROM ItineraryTable),
    INTERVAL '1 day'
) AS gs(day_date)
JOIN ItineraryTable it 
    ON gs.day_date::DATE BETWEEN it.DateFrom AND it.DateTo
ORDER BY gs.day_date::DATE ASC, it.Order ASC;

SQL Server (Using Recursive CTE)

Similar to MySQL, we'll use a recursive CTE, with an extra option to handle longer date ranges:

WITH date_range AS (
    SELECT MIN(DateFrom) AS day_date
    FROM ItineraryTable
    UNION ALL
    SELECT DATEADD(DAY, 1, day_date)
    FROM date_range
    WHERE day_date < (SELECT MAX(DateTo) FROM ItineraryTable)
)
SELECT 
    dr.day_date,
    it.ID,
    it.Title,
    it.DateFrom,
    it.DateTo,
    it.Order
FROM date_range dr
JOIN ItineraryTable it 
    ON dr.day_date BETWEEN it.DateFrom AND it.DateTo
ORDER BY dr.day_date ASC, it.Order ASC
OPTION (MAXRECURSION 0); -- Required if your date range exceeds 100 days

What This Produces #

For your sample data, the output will look like this:

• 2019-10-01: Easy Jet Flight (Order 1), Ibis Hotel (Order 2), Renting Car (Order 3)
• 2019-10-02 to 2019-10-09: Ibis Hotel (Order 2), Renting Car (Order 3)
• 2019-10-10: Ibis Hotel (Order 2), Renting Car (Order 3), Air Jet Flight (Order 4)
• And so on, until the final date in your itinerary.

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