-- 同一客户 同一房屋 对应多条合同信息
-- 查询出改客户该房屋对应的最近终止的合同
SELECT
a.rent_unit_code,
a.contract_code,
a.create_date
FROM
ais_contract a
WHERE
CONCAT(a.rent_unit_code, a.create_date) IN (
SELECT
CONCAT(
a.rent_unit_code,
max(a.create_date)
)
FROM
ais_contract a
WHERE
a.rent_unit_code IS NOT NULL and a.status_id = '10' -- 已终止
GROUP BY
a.rent_unit_code
)
AND a.rent_unit_code IS NOT NULL and a.status_id = '10'
GROUP BY
a.rent_unit_code