关联查询产生重复行，如何按TourId去重并优化Bill视图？

Hey, let's tackle this problem you're facing—your Bill view is returning duplicate rows per TourId, which happens because you're joining tables that have one-to-many relationships with the Tour table (like SpecialActivity, VisitingPlaces, or Participant). Each row in those child tables creates a new combination with the parent Tour row, leading to duplicates. Here's how to fix it and optimize your view:

1. Fix: Get One Row Per TourId #

There are two reliable ways to resolve this—choose the one that fits your data model best:

Option 1: Use Aggregation + GROUP BY #

Since you need aggregated values (like total activity costs) per Tour, wrap fields from child tables in aggregation functions (e.g., SUM, MAX) and group by the unique identifiers of the Tour table.

Here's the modified view code:

ALTER VIEW Bill AS 
SELECT 
    Tour.TourId,
    MAX(Itinerary.ItineraryId) AS ItineraryId, -- Use MAX if one Tour maps to one Itinerary
    Tour.StartDate,
    Tour.EndDate,
    DATEDIFF(day, Tour.StartDate, Tour.EndDate) AS Duration,
    MAX(Itinerary.EstTravelDist) AS EstTravelDist,
    MAX(Guide.IdNo) AS IdNo, -- Use MAX if one Tour has one Guide
    CAST(5000 * DATEDIFF(day, Tour.StartDate, Tour.EndDate) AS money) AS PaymentfoGuide,
    SUM(SpecialActivity.Cost) AS SpecielActivityCost, -- Sum all activity costs for the Tour
    SUM(VisitingPlaces.Cost) AS VisitingPlaceTicketCost, -- Sum all ticket costs
    MAX(Tour.NumberOfPeople) AS NumberOfPeople,
    -- Adjust UnitPrice source if needed (your original query didn't specify where it comes from)
    CAST(MAX(ISNULL(Meal.UnitPrice, 0)) * MAX(Tour.NumberOfPeople) AS money) AS CostForMeal,
    MAX(Accommodation.Location) AS Accomadation,
    CAST(MAX(Accommodation.UnitPrice) * MAX(Tour.NumberOfPeople) * DATEDIFF(day, Tour.StartDate, Tour.EndDate) AS money) AS TotalAccommodationCost,
    CAST(MAX(Itinerary.EstTravelDist) * 40 AS money) AS TourPackegeCost,
    SUM(SpecialActivity.Cost) * MAX(Tour.NumberOfPeople) AS TotalSpecielActivityCost,
    SUM(VisitingPlaces.Cost) * MAX(Tour.NumberOfPeople) AS TotalVisitingPlaceTicketCost,
    -- Simplified final cost calculation using aggregated values
    CAST(
        MAX(Itinerary.EstTravelDist) * 40 +
        SUM(SpecialActivity.Cost) * MAX(Tour.NumberOfPeople) +
        SUM(VisitingPlaces.Cost) * MAX(Tour.NumberOfPeople) +
        MAX(ISNULL(Meal.UnitPrice, 0)) * DATEDIFF(day, Tour.StartDate, Tour.EndDate) +
        5000 * MAX(Tour.NumberOfPeople) * DATEDIFF(day, Tour.StartDate, Tour.EndDate) +
        MAX(ISNULL(Meal.UnitPrice, 0)) * DATEDIFF(day, Tour.StartDate, Tour.EndDate) * MAX(Tour.NumberOfPeople)
        AS money
    ) AS FINAL_COST
FROM 
    Tour
    -- Use LEFT JOIN to keep Tours even if they have no matching child records
    LEFT JOIN Itinerary ON Tour.TourId = Itinerary.TourId
    LEFT JOIN SpecialActivity ON Itinerary.ItineraryId = SpecialActivity.ItineraryId
    LEFT JOIN VisitingPlaces ON VisitingPlaces.ItineraryId = Itinerary.ItineraryId
    LEFT JOIN Guide ON Guide.TourId = Tour.TourId
    LEFT JOIN Vehicle ON Vehicle.TourId = Tour.TourId
    LEFT JOIN Accommodation ON Accommodation.TourId = Tour.TourId
    LEFT JOIN Participant ON Participant.TourId = Tour.TourId
    LEFT JOIN Person ON Person.IdNo = Guide.IdNo
    LEFT JOIN Contract ON Contract.ItineraryId = Itinerary.ItineraryId
    -- Add the table that provides UnitPrice (e.g., Meal) if missing
    LEFT JOIN Meal ON Tour.TourId = Meal.TourId
GROUP BY 
    Tour.TourId, Tour.StartDate, Tour.EndDate; -- Group by unique Tour identifiers

Notes:

• Use SUM for fields that need to be aggregated across child records (like activity costs).
• Use MAX/MIN for fields that are unique per Tour (like ItineraryId or Guide.IdNo).
• Swap RIGHT JOIN with LEFT JOIN to ensure all Tours are included, even if they don't have related child records.

Option 2: Pre-Aggregate Child Tables First (More Efficient) #

Instead of aggregating after joining all tables, pre-aggregate child tables using CTEs (Common Table Expressions) to reduce the dataset size before joining. This is faster for large datasets.

ALTER VIEW Bill AS 
WITH AggregatedSpecialActivity AS (
    -- Sum activity costs per Itinerary
    SELECT ItineraryId, SUM(Cost) AS TotalSpecialActivityCost
    FROM SpecialActivity
    GROUP BY ItineraryId
),
AggregatedVisitingPlaces AS (
    -- Sum ticket costs per Itinerary
    SELECT ItineraryId, SUM(Cost) AS TotalVisitingPlaceCost
    FROM VisitingPlaces
    GROUP BY ItineraryId
),
AggregatedMeal AS (
    -- Get unique meal price per Tour
    SELECT TourId, MAX(UnitPrice) AS MealUnitPrice
    FROM Meal
    GROUP BY TourId
)
SELECT 
    Tour.TourId,
    Itinerary.ItineraryId,
    Tour.StartDate,
    Tour.EndDate,
    DATEDIFF(day, Tour.StartDate, Tour.EndDate) AS Duration,
    Itinerary.EstTravelDist,
    Guide.IdNo,
    CAST(5000 * DATEDIFF(day, Tour.StartDate, Tour.EndDate) AS money) AS PaymentfoGuide,
    COALESCE(asa.TotalSpecialActivityCost, 0) AS SpecielActivityCost, -- Handle NULLs
    COALESCE(avp.TotalVisitingPlaceCost, 0) AS VisitingPlaceTicketCost,
    Tour.NumberOfPeople,
    CAST(COALESCE(am.MealUnitPrice, 0) * Tour.NumberOfPeople AS money) AS CostForMeal,
    Accommodation.Location AS Accomadation,
    CAST(Accommodation.UnitPrice * Tour.NumberOfPeople * DATEDIFF(day, Tour.StartDate, Tour.EndDate) AS money) AS TotalAccommodationCost,
    CAST(Itinerary.EstTravelDist * 40 AS money) AS TourPackegeCost,
    COALESCE(asa.TotalSpecialActivityCost, 0) * Tour.NumberOfPeople AS TotalSpecielActivityCost,
    COALESCE(avp.TotalVisitingPlaceCost, 0) * Tour.NumberOfPeople AS TotalVisitingPlaceTicketCost,
    CAST(
        Itinerary.EstTravelDist * 40 +
        COALESCE(asa.TotalSpecialActivityCost, 0) * Tour.NumberOfPeople +
        COALESCE(avp.TotalVisitingPlaceCost, 0) * Tour.NumberOfPeople +
        COALESCE(am.MealUnitPrice, 0) * DATEDIFF(day, Tour.StartDate, Tour.EndDate) +
        5000 * Tour.NumberOfPeople * DATEDIFF(day, Tour.StartDate, Tour.EndDate) +
        COALESCE(am.MealUnitPrice, 0) * DATEDIFF(day, Tour.StartDate, Tour.EndDate) * Tour.NumberOfPeople
        AS money
    ) AS FINAL_COST
FROM 
    Tour
    LEFT JOIN Itinerary ON Tour.TourId = Itinerary.TourId
    LEFT JOIN AggregatedSpecialActivity asa ON Itinerary.ItineraryId = asa.ItineraryId
    LEFT JOIN AggregatedVisitingPlaces avp ON Itinerary.ItineraryId = avp.ItineraryId
    LEFT JOIN Guide ON Guide.TourId = Tour.TourId
    LEFT JOIN Vehicle ON Vehicle.TourId = Tour.TourId
    LEFT JOIN Accommodation ON Accommodation.TourId = Tour.TourId
    LEFT JOIN AggregatedMeal am ON Tour.TourId = am.TourId
    LEFT JOIN Participant ON Participant.TourId = Tour.TourId
    LEFT JOIN Person ON Person.IdNo = Guide.IdNo
    LEFT JOIN Contract ON Contract.ItineraryId = Itinerary.ItineraryId;

Notes:

• COALESCE replaces NULL values with 0, ensuring calculations don't break when there are no child records.
• Pre-aggregating reduces the number of rows joined, making the query faster.

2. View Optimization Tips #

• Remove Unnecessary Joins: If you're not using fields from tables like Participant or Person in your calculations, remove those joins entirely to reduce overhead.
• Add Indexes: Create indexes on join columns (e.g., Tour.TourId, Itinerary.TourId, SpecialActivity.ItineraryId) to speed up the join operations.
• Simplify Calculations: Avoid repeating the same calculation multiple times (like DATEDIFF(day, Tour.StartDate, Tour.EndDate)). You can calculate it once in a CTE and reuse it throughout the query.
• Avoid SELECT *: When querying the view, explicitly list the fields you need instead of using SELECT *—this reduces data transfer and makes the query faster.
• Check Relationship Cardinality: Confirm the relationships between tables (e.g., one Tour to one Itinerary, one Tour to many SpecialActivities) to ensure your aggregation logic is correct.

内容的提问来源于stack exchange，提问作者ray.one