如何关联CRIMES与SUSPECTS表查询最长刑期的嫌疑人？

需求回顾 #

现有CRIMES、SUSPECTS两张表，需找出拥有最长刑期的嫌疑人，结果需显示姓名、刑期的年、月、天数，要求用变量简化主查询和子查询中的重复表达式。

你之前尝试的问题点 #

• 关联表时的子查询(select end_date-start_date as Prison_date from crimes)会返回多条结果，直接嵌入触发多行子查询错误；
• 刑期转换逻辑重复调用months_between函数，代码冗余且不易维护；
• natural join依赖隐式字段匹配，易出现意外关联，建议用显式关联条件。

解决方案（以Oracle为例） #

使用**CTE（公共表表达式）**作为"变量"封装重复计算逻辑，简化代码同时提升可读性：

WITH crime_sentences AS (
    SELECT 
        crime_id,
        suspect_id, -- 假设CRIMES与SUSPECTS的关联字段为suspect_id
        months_between(end_date, start_date) AS total_months,
        TRUNC(months_between(end_date, start_date)/12) AS years,
        TRUNC(MOD(months_between(end_date, start_date), 12)) AS months,
        TRUNC((months_between(end_date, start_date) - TRUNC(months_between(end_date, start_date))) * 30) AS days
    FROM CRIMES
),
max_sentence AS (
    SELECT MAX(total_months) AS max_total FROM crime_sentences
)
SELECT 
    s.name,
    cs.years,
    cs.months,
    cs.days
FROM crime_sentences cs
JOIN SUSPECTS s ON cs.suspect_id = s.suspect_id
JOIN max_sentence ms ON cs.total_months = ms.max_total;

代码说明 #

1. crime_sentences CTE：一次性计算所有案件的总刑期月数及拆分后的年、月、天，把重复的months_between调用封装，后续查询直接引用；
2. max_sentence CTE：单独计算最长刑期的总月数，作为筛选条件的"变量"；
3. 通过显式关联字段关联两张表，筛选出刑期等于最长刑期的嫌疑人（若有多名嫌疑人刑期同为最长，会全部返回）。

兼容老版本数据库的替代方案 #

如果你的数据库不支持CTE（如老版本MySQL），可用派生表实现相同逻辑：

SELECT 
    s.name,
    cs.years,
    cs.months,
    cs.days
FROM (
    SELECT 
        crime_id,
        suspect_id,
        months_between(end_date, start_date) AS total_months,
        TRUNC(months_between(end_date, start_date)/12) AS years,
        TRUNC(MOD(months_between(end_date, start_date), 12)) AS months,
        TRUNC((months_between(end_date, start_date) - TRUNC(months_between(end_date, start_date))) * 30) AS days
    FROM CRIMES
) cs
JOIN SUSPECTS s ON cs.suspect_id = s.suspect_id
WHERE cs.total_months = (
    SELECT MAX(months_between(end_date, start_date)) FROM CRIMES
);

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