查询指定用户名下状态变更的房产默认与最新状态SQL问题

问题分析 #

你的当前查询会返回中间状态有变更但最终默认与最新状态一致的房产，核心原因是子查询sp用COUNT(DISTINCT owner) >= 2 OR COUNT(DISTINCT area) >= 2筛选了所有有过状态变动的房产——哪怕这些房产最后又变回了默认状态，比如propertyID=1，中间面积调整过但最新状态和默认完全一致，这类记录其实是我们不需要的。我们真正要抓的是默认状态和最新状态的owner/area确实存在差异的房产，只保留它们的这两种状态记录。

修正后的SQL查询 #

WITH latest_states AS (
    -- 先拿到每个房产的最新状态记录
    SELECT s.*
    FROM states s
    INNER JOIN (
        SELECT propertyID, MAX(timestamp) AS max_timestamp
        FROM states
        GROUP BY propertyID
    ) latest ON s.propertyID = latest.propertyID AND s.timestamp = latest.max_timestamp
),
default_states AS (
    -- 拿到目标用户（userID=1）名下所有房产的默认状态记录
    SELECT s.*
    FROM properties p
    INNER JOIN states s 
        ON p.defaultStateID = s.stateID AND p.defaultStateYear = s.stateYear
    WHERE p.userID = 1
),
-- 筛选出默认与最新状态存在差异的房产ID
diff_property_ids AS (
    SELECT d.propertyID
    FROM default_states d
    INNER JOIN latest_states l ON d.propertyID = l.propertyID
    WHERE d.owner != l.owner OR d.area != l.area
)
-- 最终提取这些差异房产的默认和最新状态记录
SELECT s.*
FROM states s
INNER JOIN diff_property_ids dp ON s.propertyID = dp.propertyID
WHERE 
    -- 匹配默认状态
    (s.stateID, s.stateYear) IN (SELECT stateID, stateYear FROM default_states WHERE propertyID = dp.propertyID)
    -- 匹配最新状态
    OR s.timestamp IN (SELECT timestamp FROM latest_states WHERE propertyID = dp.propertyID)
ORDER BY s.propertyID ASC, s.stateYear ASC, s.stateID ASC;

逻辑拆解 #

1. latest_states：通过每个房产的最大timestamp，精准锁定该房产的最新状态，避免中间状态干扰。
2. default_states：直接关联properties和states表，只提取目标用户名下房产的默认状态数据。
3. diff_property_ids：对比默认状态和最新状态的owner与area，只留下两者有差异的房产ID，这一步是过滤的核心。
4. 最后一步：从states表中抓取这些差异房产的默认状态和最新状态记录，按要求排序输出。

执行结果 #

运行后会得到你预期的结果：

stateID propertyID owner         area timestamp  stateYear
2       2          Amanda Green  74   123456799  2017
4       2          Amanda Green  73   129999999  2018

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