如何用MySQL查询2020及2021年都有出售记录的同一房产

开发者阿泽

2026-10-1

MySQL筛选两年均有交易记录的房产方案

我们默认你的房屋出售记录表名为 house_sales，存储交易时间的字段名为 sale_date，如果你的实际表名、字段名不同，替换对应部分即可。

方案1：分组聚合筛选（最简洁，适合仅需获取房产唯一信息的场景）

SELECT number, street, town, county, postcode
FROM house_sales
WHERE YEAR(sale_date) IN (2020, 2021)
GROUP BY number, street, town, county, postcode
HAVING COUNT(DISTINCT YEAR(sale_date)) = 2;

逻辑说明

先过滤出所有2020、2021年的交易记录，减少后续计算量
按你指定的5个房产唯一标识字段分组，每个分组对应同一个房产
最后判断每个分组内是否同时包含2020、2021两个年份的记录，满足条件的即为目标房产

如果需要获取这些房产对应的所有交易明细，可以用子查询配合IN实现：

SELECT *
FROM house_sales
WHERE (number, street, town, county, postcode) IN (
    SELECT number, street, town, county, postcode
    FROM house_sales
    WHERE YEAR(sale_date) IN (2020, 2021)
    GROUP BY number, street, town, county, postcode
    HAVING COUNT(DISTINCT YEAR(sale_date)) = 2
);

方案2：自连接查询（适合需要同时查看两年交易数据的场景）

SELECT DISTINCT 
  h1.number, h1.street, h1.town, h1.county, h1.postcode,
  h1.sale_date AS sale_date_2020, h1.price AS price_2020,
  h2.sale_date AS sale_date_2021, h2.price AS price_2021
FROM house_sales h1
INNER JOIN house_sales h2
  ON h1.number = h2.number
  AND h1.street = h2.street
  AND h1.town = h2.town
  AND h1.county = h2.county
  AND h1.postcode = h2.postcode
  AND YEAR(h1.sale_date) = 2020
  AND YEAR(h2.sale_date) = 2021;