如何使用MySQL展示多同结构年度表中的属性状态变化？

嗨，针对你的问题，我来分享几种在MySQL中展示属性年度状态变化的方法，结合你的场景一步步来：

1. 先明确表格结构（基于你的描述） #

首先我们先定义示例中涉及的三张表结构，方便后续理解：

-- 存储属性基础信息的表
CREATE TABLE properties (
    propertyID INT PRIMARY KEY,
    propertyName VARCHAR(100) -- 示例字段，可根据实际业务调整
);

-- 2017年度属性状态表
CREATE TABLE states_2017 (
    propertyID INT,
    stateID INT,
    PRIMARY KEY (propertyID),
    FOREIGN KEY (propertyID) REFERENCES properties(propertyID)
);

-- 2018年度属性状态表（结构和2017完全一致）
CREATE TABLE states_2018 (
    propertyID INT,
    stateID INT,
    PRIMARY KEY (propertyID),
    FOREIGN KEY (propertyID) REFERENCES properties(propertyID)
);

2. 基础查询：展示所有属性的两年状态对比 #

这个查询会关联属性表和两年的状态表，直观展示每个属性的状态变化情况：

SELECT
    p.propertyID,
    p.propertyName,
    s17.stateID AS state_2017,
    s18.stateID AS state_2018,
    -- 用CASE语句明确标记状态是否变化
    CASE
        WHEN s17.stateID != s18.stateID THEN '*状态已变更*'
        ELSE '状态未变更'
    END AS change_status
FROM
    properties p
-- 使用LEFT JOIN确保即使某一年没有状态记录（如新属性）也能被展示
LEFT JOIN states_2017 s17 ON p.propertyID = s17.propertyID
LEFT JOIN states_2018 s18 ON p.propertyID = s18.propertyID
-- 如果只想筛选出有变化的属性，取消下面的注释
-- WHERE s17.stateID != s18.stateID;

关键说明： #

• LEFT JOIN避免遗漏那些在某一年没有状态数据的属性（比如2018年新增的属性）
• CASE语句让状态变化的标记更直观，用星号强调了变化状态
• 取消WHERE子句的注释可以只查看状态发生变更的属性

3. 进阶：展示状态的具体名称（如果有状态字典表） #

如果你的系统中有存储stateID对应名称的字典表（比如state_names），可以进一步关联让结果更易读：

-- 先创建状态字典表示例
CREATE TABLE state_names (
    stateID INT PRIMARY KEY,
    stateName VARCHAR(50) -- 比如"正常"、"停用"等状态名称
);

-- 查询带状态名称的变化记录
SELECT
    p.propertyID,
    p.propertyName,
    sn17.stateName AS state_2017_name,
    sn18.stateName AS state_2018_name,
    CASE
        WHEN s17.stateID != s18.stateID THEN '*状态已变更*'
        ELSE '状态未变更'
    END AS change_status
FROM
    properties p
LEFT JOIN states_2017 s17 ON p.propertyID = s17.propertyID
LEFT JOIN state_names sn17 ON s17.stateID = sn17.stateID
LEFT JOIN states_2018 s18 ON p.propertyID = s18.propertyID
LEFT JOIN state_names sn18 ON s18.stateID = sn18.stateID
WHERE s17.stateID != s18.stateID;

4. 优化建议：如果有多个年度数据 #

如果你的系统中有很多年度的状态表（比如2017-2023），不建议每年新建一张表，而是改成一张带年份字段的单表：

-- 优化后的状态表结构
CREATE TABLE property_states (
    propertyID INT,
    year INT,
    stateID INT,
    PRIMARY KEY (propertyID, year), -- 复合主键确保每个属性每年只有一条状态记录
    FOREIGN KEY (propertyID) REFERENCES properties(propertyID)
);

这种结构下，查询任意连续年份的状态变化会更灵活：

SELECT
    p.propertyID,
    p.propertyName,
    ps_prev.year AS previous_year,
    ps_prev.stateID AS previous_state,
    ps_curr.year AS current_year,
    ps_curr.stateID AS current_state,
    CASE
        WHEN ps_prev.stateID != ps_curr.stateID THEN '*状态已变更*'
        ELSE '状态未变更'
    END AS change_status
FROM
    properties p
JOIN property_states ps_curr ON p.propertyID = ps_curr.propertyID
JOIN property_states ps_prev ON p.propertyID = ps_prev.propertyID 
    AND ps_curr.year = ps_prev.year + 1 -- 关联上一年的状态记录
WHERE ps_prev.stateID != ps_curr.stateID;

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