如何获取properties表与states表中的差异数据行？

Hey there! Let's work through how to find the differing rows between your properties and states tables. First, I'll make some reasonable assumptions about your table structures since you didn't list all the fields—this will help craft accurate, usable queries.

假设的表结构 #

-- properties表：存储属性的基础信息与默认状态关联
CREATE TABLE properties (
    property_id INT PRIMARY KEY, -- 属性ID
    user_id INT, -- 属性所属用户ID
    default_state_id INT, -- 默认状态ID
    default_state_year INT -- 默认状态年份
);

-- states表：存储属性各时间点的动态数据
CREATE TABLE states (
    state_id INT,
    state_year INT,
    property_id INT, -- 关联properties的property_id
    owner_user_id INT, -- 当前状态的所有者ID
    area DECIMAL(10,2), -- 面积
    -- 其他动态字段...
    PRIMARY KEY (state_id, state_year) -- 组合唯一键
);

接下来，我们要找的差异通常分为几种常见场景，我会分别给出对应的SQL查询：

场景1：Properties的默认状态在States表中不存在 #

这种情况是properties里的default_state_id+default_state_year组合，在states表中找不到对应的记录——属于无效的默认状态关联：

SELECT p.*
FROM properties p
LEFT JOIN states s
    ON p.default_state_id = s.state_id 
    AND p.default_state_year = s.state_year
WHERE s.state_id IS NULL;

场景2：默认状态的所有者与属性所属用户不一致 #

Since you mentioned each property belongs to a specific user, the owner in the default state should match the property's assigned user. If they don't, that's a clear discrepancy:

SELECT 
    p.property_id,
    p.user_id AS property_assigned_user,
    s.owner_user_id AS default_state_owner,
    p.default_state_id,
    p.default_state_year
FROM properties p
JOIN states s
    ON p.default_state_id = s.state_id 
    AND p.default_state_year = s.state_year
WHERE p.user_id != s.owner_user_id;

场景3：States中存在属性的非默认状态数据 #

如果你想找出所有不属于属性默认状态的states记录（这些是属性的历史/其他状态，和默认状态天然存在差异），用这个查询：

SELECT s.*
FROM states s
JOIN properties p
    ON s.property_id = p.property_id
WHERE NOT (s.state_id = p.default_state_id AND s.state_year = p.default_state_year);

场景4：默认状态与属性其他状态的字段差异（比如面积） #

If you want to compare specific field values (like area) between the default state and other states for each property, this query will list them side-by-side for easy comparison:

SELECT 
    p.property_id,
    '默认状态' AS state_category,
    s_default.area AS area_value
FROM properties p
JOIN states s_default
    ON p.default_state_id = s_default.state_id 
    AND p.default_state_year = s_default.state_year
UNION ALL
SELECT 
    p.property_id,
    '其他状态' AS state_category,
    s_other.area AS area_value
FROM properties p
JOIN states s_other
    ON p.property_id = s_other.property_id
WHERE NOT (s_other.state_id = p.default_state_id AND s_other.state_year = p.default_state_year)
ORDER BY property_id, state_category;

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