如何按条件关联两张表？关联求和查询结果异常求助

Hey there! Let's break down your problem step by step and fix that incorrect query, plus cover different ways to join tables with varying conditions.

First: Why Your Current Query Isn't Working #

Your right join approach has two key issues that throw off your sums:

1. Unintended filtering: By adding date conditions for both tables in the WHERE clause, you're effectively turning your RIGHT JOIN into an INNER JOIN—any rows from TBL_SALES_INFO that don't have a matching row in TBL_SALES get filtered out.
2. Duplicate calculation: If SALES_NO has a one-to-many or many-to-many relationship between the two tables, joining first and then summing will count values multiple times. For example, if one SALES_NO in TBL_SALES links to 3 rows in TBL_SALES_INFO, your SUM(A.TOTAL_SALES) will add that sales value 3 times instead of once.

The Correct Approach: Aggregate First, Join Second #

To get accurate totals, first calculate the sums for each table independently, then join those aggregated results. Here are two scenarios:

Scenario 1: Total sums for the date range (no grouping by SALES_NO) #

SELECT 
    COALESCE(sales_agg.total_sales, 0) AS total_sales,
    COALESCE(info_agg.total_money, 0) AS total_money
FROM 
    (SELECT SUM(TOTAL_SALES) AS total_sales 
     FROM TBL_SALES 
     WHERE LOGIN_DATE >= '2020-01-03' AND LOGOUT_DATE < '2020-01-04') AS sales_agg
FULL JOIN 
    (SELECT SUM(TOTAL_MONEY) AS total_money 
     FROM TBL_SALES_INFO 
     WHERE LOGIN_DATE >= '2020-01-03' AND LOGOUT_DATE < '2020-01-04') AS info_agg
ON 1=1; -- Constant condition to join the single-row aggregates

We use COALESCE to replace NULL with 0 if one table has no matching data for the date range.

Scenario 2: Sums grouped by SALES_NO #

If you need totals per individual sales number:

SELECT 
    COALESCE(a.sales_no, b.sales_no) AS sales_no,
    COALESCE(a.total_sales, 0) AS total_sales,
    COALESCE(b.total_money, 0) AS total_money
FROM 
    (SELECT SALES_NO, SUM(TOTAL_SALES) AS total_sales 
     FROM TBL_SALES 
     WHERE LOGIN_DATE >= '2020-01-03' AND LOGOUT_DATE < '2020-01-04'
     GROUP BY SALES_NO) AS a
FULL JOIN 
    (SELECT SALES_NO, SUM(TOTAL_MONEY) AS total_money 
     FROM TBL_SALES_INFO 
     WHERE LOGIN_DATE >= '2020-01-03' AND LOGOUT_DATE < '2020-01-04'
     GROUP BY SALES_NO) AS b
ON a.sales_no = b.sales_no;

Different Ways to Join Tables With Custom Conditions #

Here are common techniques for joining tables based on varying criteria:

1. Filter During Join (in the ON clause) #

Put table-specific filtering directly in the ON clause to control which rows get matched, rather than filtering the final result:

SELECT *
FROM TBL_SALES a
JOIN TBL_SALES_INFO b
    ON a.sales_no = b.sales_no
    AND a.login_date >= '2020-01-01' -- Filter TBL_SALES rows before joining
WHERE b.logout_date < '2020-02-01'; -- Filter final result for TBL_SALES_INFO

2. Use Different Join Types for Matching Needs #

Choose the join type based on which rows you need to retain:

• INNER JOIN: Only returns rows where both tables have matching SALES_NO (use when you need data from both tables).
• LEFT JOIN: Returns all rows from TBL_SALES, plus matching rows from TBL_SALES_INFO (use when you need to retain all sales records, even if no corresponding info exists).
• RIGHT JOIN: Returns all rows from TBL_SALES_INFO, plus matching rows from TBL_SALES (opposite of LEFT JOIN).
• FULL JOIN: Returns all rows from both tables, matching where possible (use when you need to retain all records from both tables).

3. Join on Multiple Conditions #

If you need to match on more than one field, combine conditions with AND in the ON clause:

SELECT *
FROM TBL_SALES a
JOIN TBL_SALES_INFO b
    ON a.sales_no = b.sales_no
    AND a.user_id = b.user_id; -- Match both sales number and user ID

4. Pre-Filter Tables With Subqueries #

For complex filters, pre-filter each table in a subquery before joining to reduce the data being processed:

SELECT *
FROM (
    SELECT * FROM TBL_SALES 
    WHERE login_date >= '2020-01-03' AND logout_date < '2020-01-04'
) AS filtered_sales
JOIN (
    SELECT * FROM TBL_SALES_INFO 
    WHERE total_money > 100
) AS filtered_info
ON filtered_sales.sales_no = filtered_info.sales_no;

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