PostgreSQL 9.5：筛选符合关联条件或无关联的rate_adjust记录

Let's fix your query step by step, and also cover a PL/pgSQL alternative if you prefer that approach.

What's Wrong With Your Current Query #

Your original attempt has a few key issues that are causing incorrect results:

1. Broken JOIN syntax: You nested a WHERE clause inside the LEFT JOIN condition, which disrupts the left join's logic and filters records prematurely.
2. NULL handling for unassociated records: When a rate_adjust has no matching factor_calendar entries, all fc columns are NULL. Your range overlap condition evaluates to NULL in this case, and NULL in a WHERE clause is treated as false—so these valid unassociated records get filtered out.
3. Duplicate rows: If a rate_adjust has multiple linked factor_calendar entries, your query would return duplicate copies of the same rate_adjust record.

Correct SQL Query (Matches Your Stated Requirements) #

This query returns rate_adjust records where:

• The product_id matches your input p_id
• Either the record has no associated factor_calendar entries, or at least one associated entry overlaps with your specified start_day_id and end_day_id

SELECT DISTINCT rad.*
FROM rate_adjust AS rad
LEFT JOIN factor_calendar AS fc 
  ON rad.id = fc.rate_adjust_id
WHERE rad.product_id = p_id
  AND start_day_id < end_day_id
  AND (
    -- Capture records with no linked factor_calendar entries
    fc.id IS NULL
    -- OR capture records where at least one factor_calendar entry overlaps the date range
    OR (fc.from_day <= end_day_id AND fc.thru_day >= start_day_id)
  );

Quick Explanation:

• DISTINCT: Ensures we don't get duplicate rate_adjust rows if a record has multiple matching factor_calendar entries.
• fc.id IS NULL: Picks up rate_adjust records with no linked calendar entries (like id=4 in your sample data).
• fc.from_day <= end_day_id AND fc.thru_day >= start_day_id: This is the standard way to check for interval overlap—two ranges [a,b] and [c,d] overlap if the start of one is <= the end of the other, and vice versa.

Matching Your Expected Output #

Your expected result (id=1, 2, 4 when p_id=3, start_day_id=4, end_day_id=15) doesn't align with the stated requirements—since id=3 has an overlapping calendar entry (1-7 overlaps 4-15) and should be included. If you intentionally want to exclude id=3, there's likely an unstated condition (like filtering by type). For example, if you want to keep type=0 records regardless of calendar entries, and only keep type=1 records if they have no calendar links, use this adjusted query:

SELECT DISTINCT rad.*
FROM rate_adjust AS rad
LEFT JOIN factor_calendar AS fc 
  ON rad.id = fc.rate_adjust_id
WHERE rad.product_id = p_id
  AND start_day_id < end_day_id
  AND (
    -- Keep all type=0 records
    rad.type = 0
    -- Keep type=1 records only if they have no linked calendar entries
    OR (rad.type = 1 AND fc.id IS NULL)
  );

This will return exactly your expected id=1, 2, 4.

PL/pgSQL Function Alternative #

If you prefer using a function for reusability, here's a PL/pgSQL implementation that matches your stated requirements (adjust the WHERE clause inside if you need to match your expected output):

CREATE OR REPLACE FUNCTION get_rate_adjustments(p_id INT, start_day_id INT, end_day_id INT)
RETURNS SETOF rate_adjust AS $$
BEGIN
  RETURN QUERY
  SELECT DISTINCT rad.*
  FROM rate_adjust AS rad
  LEFT JOIN factor_calendar AS fc 
    ON rad.id = fc.rate_adjust_id
  WHERE rad.product_id = p_id
    AND start_day_id < end_day_id
    AND (
      fc.id IS NULL
      OR (fc.from_day <= end_day_id AND fc.thru_day >= start_day_id)
    );
END;
$$ LANGUAGE plpgsql;

-- To use the function:
SELECT * FROM get_rate_adjustments(3, 4, 15);

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