基于双表的CASE语句实现赛马繁育开放年份计算需求

要搞定这个赛马繁育资格的筛选和年份计算问题，我们得把Horses的基础信息和Results的比赛记录结合起来，一步步拆解条件来实现。下面是具体的思路和SQL代码：

核心逻辑拆解 #

先把繁育的几个硬条件理清楚：

• 赛马年龄得≥3岁：也就是出生年份（YOB）加3后的年份，这是最早能开放繁育的起始点
• 得在最后一次参赛的年份后推1年：保证赛马彻底退役后再考虑繁育
• 繁育开放的那一年，这匹马不能有后代出生：也就是当年没有以它为父（SireID）或母（DamID）的小马出生

完整SQL实现 #

WITH HorseLastRace AS (
    -- 第一步：先找出每匹赛马的最后参赛年份
    SELECT HID, MAX(YEAR(Date2)) AS LastRaceYear
    FROM Results
    GROUP BY HID
),
HorsePotentialOpen AS (
    -- 第二步：计算每匹马的候选繁育开放年份，同时推导性别（原表没Gender字段，靠后代关系判断）
    SELECT
        h.HID,
        h.HName AS HorseName,
        h.YOB,
        CASE
            WHEN EXISTS (SELECT 1 FROM Horses WHERE SireID = h.HID) THEN 'Stallion'
            WHEN EXISTS (SELECT 1 FROM Horses WHERE DamID = h.HID) THEN 'Mare'
            ELSE 'Unknown'
        END AS Gender,
        -- 取"满3岁年份"和"最后参赛后1年"里更大的那个作为候选开放年
        GREATEST(h.YOB + 3, COALESCE(hlr.LastRaceYear + 1, h.YOB + 3)) AS ProposedOpenYear
    FROM Horses h
    LEFT JOIN HorseLastRace hlr ON h.HID = hlr.HID
    -- 先过滤掉还没满3岁的赛马
    WHERE h.YOB + 3 <= YEAR(CURDATE())
),
HorseOpenYears AS (
    -- 第三步：筛掉候选年份里有后代出生的赛马
    SELECT
        HorseName,
        YOB,
        Gender,
        ProposedOpenYear AS `YEARS OPEN`
    FROM HorsePotentialOpen hpo
    WHERE NOT EXISTS (
        SELECT 1
        FROM Horses offspring
        WHERE (offspring.SireID = hpo.HID OR offspring.DamID = hpo.HID)
          AND offspring.YOB = hpo.ProposedOpenYear
    )
)
SELECT * FROM HorseOpenYears;

代码细节说明 #

1. HorseLastRace：通过分组聚合拿到每匹马的最后参赛年份，处理同一匹马多次参赛的情况
2. HorsePotentialOpen：
   • 用GREATEST()确保同时满足年龄和退役时间两个要求
   • 因为原表没Gender字段，通过判断这匹马是否是其他马的父/母来推断性别
   • 用COALESCE()处理没参赛记录的赛马，直接用满3岁的年份当候选
3. HorseOpenYears：用NOT EXISTS子查询排除掉候选年份有后代出生的赛马

测试结果 #

用你提供的测试数据跑这段SQL，会得到以下结果：

原因很简单：Stratosphere最后一次参赛是2007年，后推1年是2008年；它的后代都出生在2011和2012年，2008年没后代，而且2001+3=2004≤2008，完全符合所有繁育条件。

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