MariaDB子查询报错：WHERE子句中存在未知列p.id

你遇到的错误根源是SQL的执行逻辑：FROM子句里并列的子查询，在执行阶段还没和主表p完成关联，所以无法识别p.id这个字段。所有需要引用主表字段的子查询，都得改成JOIN关联的形式，包括你提到的聚合类子查询，具体改造方法如下：

核心改造思路 #

把FROM里的所有子查询，全部移到JOIN语句中，通过ON条件和主表p关联，这样子查询就能正常引用p.id了。

逐个改造子查询 #

1. owner_now（最新产权人） #

可以用LEFT JOIN关联子查询，或者用窗口函数ROW_NUMBER()让逻辑更简洁：

LEFT JOIN (
    SELECT po.*, ROW_NUMBER() OVER(PARTITION BY po.property_id ORDER BY po.id DESC) AS rn
    FROM `property_owners` po
) AS owner_now ON owner_now.property_id = p.id AND owner_now.rn = 1

2. prev_owner（历史产权人） #

同样用窗口函数筛选出非最新的记录，再关联主表：

LEFT JOIN (
    SELECT po.*, ROW_NUMBER() OVER(PARTITION BY po.property_id ORDER BY po.id DESC) AS rn
    FROM `property_owners` po
) AS prev_owner ON prev_owner.property_id = p.id AND prev_owner.rn > 1

如果只需要倒数第二个产权人，把rn >1改成rn=2即可。

3. rental（租赁统计） #

聚合类子查询直接改成LEFT JOIN，通过property_id关联主表，因为按property_id分组，每个property只会返回一条统计结果：

LEFT JOIN (
    SELECT 
        property_id,
        SUM(monthly_rental) as `mons`, 
        SUM(deposit) as `deps`, 
        SUM(area) as `areas`
    FROM `rental_histories`
    GROUP BY property_id
) AS rental ON rental.property_id = p.id

4. land_specifics（土地面积统计） #

和上面的聚合子查询逻辑一致：

LEFT JOIN (
    SELECT 
        property_id,
        SUM(area) as `land_area`
    FROM `property_land_specifics`
    GROUP BY property_id
) AS land_specifics ON land_specifics.property_id = p.id

完整改造后的SQL #

SELECT 
    p.*,
    owner_now.id AS latest_owner_id, owner_now.name AS latest_owner_name, -- 按需选择字段，避免*带来冗余
    prev_owner.id AS prev_owner_id, prev_owner.name AS prev_owner_name,
    rental.mons, rental.deps, rental.areas,
    land_specifics.land_area
FROM `properties` p
INNER JOIN `users` u ON u.id = p.user_id
INNER JOIN `property_prices` pp ON pp.property_id = p.id
INNER JOIN `property_lands` pl ON pl.property_id = p.id
INNER JOIN `land_usages` lu ON pl.usage_id = lu.id
-- 新增的JOIN语句放在主表关联之后
LEFT JOIN (
    SELECT po.*, ROW_NUMBER() OVER(PARTITION BY po.property_id ORDER BY po.id DESC) AS rn
    FROM `property_owners` po
) AS owner_now ON owner_now.property_id = p.id AND owner_now.rn = 1
LEFT JOIN (
    SELECT po.*, ROW_NUMBER() OVER(PARTITION BY po.property_id ORDER BY po.id DESC) AS rn
    FROM `property_owners` po
) AS prev_owner ON prev_owner.property_id = p.id AND prev_owner.rn > 1
LEFT JOIN (
    SELECT 
        property_id,
        SUM(monthly_rental) as `mons`, 
        SUM(deposit) as `deps`, 
        SUM(area) as `areas`
    FROM `rental_histories`
    GROUP BY property_id
) AS rental ON rental.property_id = p.id
LEFT JOIN (
    SELECT 
        property_id,
        SUM(area) as `land_area`
    FROM `property_land_specifics`
    GROUP BY property_id
) AS land_specifics ON land_specifics.property_id = p.id

WHERE ... -- 保留你的原有筛选条件

额外说明 #

• 用LEFT JOIN而非INNER JOIN，是为了避免过滤掉没有产权人、租赁记录或土地详情的property；如果业务要求必须有这些数据，再改成INNER JOIN。
• 窗口函数ROW_NUMBER()需要MySQL 8.0+版本支持，低版本MySQL可以用子查询关联实现：

  -- 低版本MySQL实现owner_now
  LEFT JOIN `property_owners` owner_now 
      ON owner_now.property_id = p.id 
      AND owner_now.id = (SELECT MAX(id) FROM `property_owners` WHERE property_id = p.id)
  

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