T-SQL中Union返回重复日期行，求特定日期范围每日求和解决方案

Got it, let's tackle that duplicate row problem you're hitting with the UNION approach. First, let's break down why those duplicates are happening: when a date exists in both your pre-filled date table and your business data table, the UNION (or UNION ALL) will return two rows for that date—one with NULL for visitors from the date table, and one with the actual visitor count from the business data. That's exactly the duplication you're seeing.

The Better Approach: Left Join with Aggregation #

You mentioned a LEFT JOIN didn't work earlier, but odds are it's because you didn't first aggregate your business data to get a single row per date. Here's the correct way to structure this query:

1. First, aggregate your business data to calculate total visitors per day (this ensures one row per date in the business data):

   SELECT 
       date AS Day,
       SUM(Visitors) AS TotalVisitors
   FROM your_business_table
   WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' -- Replace with your target date range
   GROUP BY date
   

2. Join this aggregated data to your date dimension table:

   SELECT 
       COALESCE(b.TotalVisitors, 0) AS Visitors, -- Use COALESCE to turn NULLs into 0 if needed; omit to keep NULL
       d.date AS Day
   FROM your_date_dimension_table d
   LEFT JOIN (
       -- Subquery with aggregated business data
       SELECT 
           date AS Day,
           SUM(Visitors) AS TotalVisitors
       FROM your_business_table
       WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
       GROUP BY date
   ) b ON d.date = b.Day
   WHERE d.date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD' -- Filter to your target date range
   ORDER BY d.date;
   

This works because:

• The date dimension table provides every date in your range, no exceptions.
• The LEFT JOIN pulls in the aggregated visitor count for dates that have business data, and leaves NULL (or 0, thanks to COALESCE) for dates that don't.
• Each date appears exactly once in the final result—no duplicates.

If You Insist on Using UNION (Not Recommended) #

If you want to stick with the union approach for some reason, you can fix duplicates by wrapping the union in an aggregation step:

SELECT 
    COALESCE(SUM(Visitors), 0) AS Visitors,
    Day
FROM (
    -- Get all dates from the dimension table
    SELECT NULL AS Visitors, D.DATE AS Day 
    FROM your_date_dimension_table D
    WHERE D.DATE BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
    UNION ALL
    -- Get aggregated business data
    SELECT SUM(Visitors) AS Visitors, date AS Day
    FROM your_business_table
    WHERE date BETWEEN 'YYYY-MM-DD' AND 'YYYY-MM-DD'
    GROUP BY date
) combined_results
GROUP BY Day
ORDER BY Day;

This groups the combined rows by date, summing the visitor counts (which effectively replaces the NULL from the date table with the actual business data count for overlapping dates). But the LEFT JOIN method is cleaner and more efficient.

Quick Check for Your Earlier LEFT JOIN Failure #

If your first LEFT JOIN attempt returned duplicates, it's almost certainly because you didn't aggregate the business data first. If your business table has multiple rows per date (e.g., multiple entries for the same day), the LEFT JOIN would return one row per business entry, causing duplicates. Aggregating first fixes that.

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