如何在关联三表的统计MySQL查询中添加BETWEEN日期条件？

Hey there! Let's get that date range filter applied to your existing MySQL query so all your counts are limited to trips between 2018-03-20 and 2018-03-23. I'll walk you through how to apply the condition to both the main query and subqueries, plus share an optimized version to keep things clean.

Modified Query with Explicit Date Conditions #

First, here's the adjusted query where we add the tripDate BETWEEN condition to every relevant part of the query:

SELECT 
    COUNT(T.tripId) as Escort_Count,
    (
        SELECT COUNT(*) FROM (
            SELECT a.allocationId 
            FROM escort_allocation a
            INNER JOIN cab_allocation c ON a.allocationId = c.allocationId
            -- Join to trip_details to apply date filter
            INNER JOIN trip_details td ON a.allocationId = td.allocationId
            WHERE c.allocationType = 'Adhoc Trip'
              AND td.tripDate BETWEEN '2018-03-20' AND '2018-03-23'
            GROUP BY a.allocationId
        ) AS Ad
    ) AS Adhoc_Trip_Count,
    (
        SELECT COUNT(id) 
        FROM trip_details
        WHERE tripDate BETWEEN '2018-03-20' AND '2018-03-23'
    ) as Total_Count
FROM (
    SELECT a.tripId 
    FROM trip_details a
    INNER JOIN escort_allocation b ON a.allocationId = b.allocationId
    -- Filter dates directly in the main subquery
    WHERE a.tripDate BETWEEN '2018-03-20' AND '2018-03-23'
    GROUP BY a.allocationId
) AS T

Breakdown of Condition Application #

Let's go through each section to see how the date filter is used:

• Main Query Subquery (T): We add the tripDate condition directly to the trip_details WHERE clause here, ensuring we only count trips with escort allocations that fall within your target date range.
• Adhoc_Trip_Count Subquery: Since this subquery originally only joined escort_allocation and cab_allocation, we need to add a join to trip_details to access the tripDate field. This lets us filter adhoc trips to the same date range as the rest of the stats.
• Total_Count Subquery: Simple enough—just add the date condition to the trip_details count query to get total trips in the range.

Optimized Version with CTE (Cleaner & Easier to Maintain) #

To avoid repeating the date filter multiple times, you can use a Common Table Expression (CTE) to pre-filter the relevant trip_details records. This makes the query more readable and easier to update if you need to change the date range later:

-- Define a CTE to hold only trips in our target date range
WITH DateFilteredTrips AS (
    SELECT * FROM trip_details 
    WHERE tripDate BETWEEN '2018-03-20' AND '2018-03-23'
)
SELECT 
    COUNT(T.tripId) as Escort_Count,
    (
        SELECT COUNT(*) FROM (
            SELECT a.allocationId 
            FROM escort_allocation a
            INNER JOIN cab_allocation c ON a.allocationId = c.allocationId
            INNER JOIN DateFilteredTrips td ON a.allocationId = td.allocationId
            WHERE c.allocationType = 'Adhoc Trip'
            GROUP BY a.allocationId
        ) AS Ad
    ) AS Adhoc_Trip_Count,
    (SELECT COUNT(id) FROM DateFilteredTrips) as Total_Count
FROM (
    SELECT df.tripId 
    FROM DateFilteredTrips df
    INNER JOIN escort_allocation b ON df.allocationId = b.allocationId
    GROUP BY df.allocationId
) AS T

This way, you only set the date range once in the CTE, and all parts of the query reference that pre-filtered dataset. No more copying and pasting the same condition!

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