如何筛选符合条件的首/次新/最新日期及解决单记录无输出问题

Let's break down your core needs and fix the SQL queries to meet your requirements properly:

What You're Trying to Achieve #

For each Reg ID:

• Pull the latest AI date (LastestAIDate), second latest AI date (PreviousAIDate) from table1 (your x_animal_breeding_rec table)
• Get the first AI date that's later than the latest calving date (1stDateOfAI) from table2 (your x_animal_calving_rec table)
• Ensure results are returned even when there's only 1 AI record for a Reg ID (if it meets the date condition)

Current Issues with Your Queries #

• Your first query uses an inner join on the breeding table, which filters out Reg IDs with only 1 AI record (no matching rows in the joined subquery)
• It also counts the wrong number of records (it counts matches from the joined table, not total AI records per Reg ID)
• Your second query has flawed ranking logic (it ranks by recID instead of grouping by actualDam), leading to incorrect date values even though the count is right

Sample Data & Expected Output #

table1 (x_animal_breeding_rec)

table2 (x_animal_calving_rec)

Expected Output

Fixed SQL Query Using Window Functions #

Window functions are perfect here because they let us rank records per Reg ID without filtering out rows (like inner joins do). Here's the corrected query:

WITH ranked_breeding AS (
    -- Rank AI dates per animal, get total records count
    SELECT
        a.regID,
        b.actualDam,
        b.breedDate,
        -- Rank dates from newest to oldest
        ROW_NUMBER() OVER (PARTITION BY b.actualDam ORDER BY b.breedDate DESC) AS rn_desc,
        -- Rank dates from oldest to newest
        ROW_NUMBER() OVER (PARTITION BY b.actualDam ORDER BY b.breedDate ASC) AS rn_asc,
        -- Total AI records per animal
        COUNT(b.breedDate) OVER (PARTITION BY b.actualDam) AS total_ai_records
    FROM x_animal_breeding_rec b
    LEFT JOIN x_animal_main_info a 
        ON b.actualDam = a.animalID
    LEFT JOIN x_herd_animal_rel r 
        ON b.actualDam = r.animal
    LEFT JOIN x_herd h 
        ON r.herd = h.herdID
    WHERE h.herdID = ? 
      AND b.mateType = ? 
      AND b.recFlag = ?
),
latest_calving_dates AS (
    -- Get the most recent calving date per Reg ID
    SELECT
        a.regID,
        MAX(c.calvingDate) AS LatestDateOfCalving
    FROM x_animal_calving_rec c
    -- Adjust the join logic here if your actual table relationships differ
    LEFT JOIN x_animal_breeding_rec b 
        ON c.brecID = b.recID
    LEFT JOIN x_animal_main_info a 
        ON b.actualDam = a.animalID
    GROUP BY a.regID
)
-- Aggregate the final required fields
SELECT
    rb.regID,
    lcd.LatestDateOfCalving,
    -- Get the first AI date after the latest calving date
    (SELECT breedDate 
     FROM ranked_breeding 
     WHERE regID = rb.regID 
       AND breedDate > lcd.LatestDateOfCalving 
     ORDER BY breedDate ASC LIMIT 1) AS 1stDateOfAI,
    -- Get second latest AI date (or NULL if only 1 record exists)
    CASE 
        WHEN rb.total_ai_records >= 2 THEN 
            (SELECT breedDate FROM ranked_breeding WHERE regID = rb.regID AND rn_desc = 2)
        ELSE NULL 
    END AS PreviousAIDate,
    -- Get the latest AI date
    (SELECT breedDate FROM ranked_breeding WHERE regID = rb.regID AND rn_desc = 1) AS LastestAIDate
FROM ranked_breeding rb
LEFT JOIN latest_calving_dates lcd 
    ON rb.regID = lcd.regID
GROUP BY rb.regID, lcd.LatestDateOfCalving, rb.total_ai_records
-- Only keep records where the first AI date is after the latest calving date
HAVING 1stDateOfAI IS NOT NULL;

Key Fixes & Explanations #

1. ranked_breeding CTE:
   • Uses ROW_NUMBER() to rank AI dates in both ascending and descending order per animal, making it easy to pull the latest, second latest, and earliest dates
   • COUNT() OVER (PARTITION BY b.actualDam) gives the total number of AI records per animal, so we can handle single-record cases gracefully
2. latest_calving_dates CTE:
   • Aggregates to get the most recent calving date per Reg ID, which we use as our reference date
3. Main Query:
   • Subqueries fetch the required dates based on the rankings
   • The CASE statement handles single-record scenarios by returning NULL for PreviousAIDate
   • The HAVING clause filters out any Reg IDs where there are no AI dates after the latest calving date
   • No inner joins are used here, so even animals with only 1 AI record will appear in results if they meet the date condition

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