「问题」Mapper层无法正确映射子查询的结果的解决办法

在 SQL 查询中，如果将子查询放在 SELECT 语句的最后一行作为字段，而不是在 FROM 子句中连接表时，可能会导致无法正确映射的问题。这是因为在很多 ORM 框架中，它们通常只能处理简单的查询，即将结果映射到对象的属性上。

原始SQL 

select id,
       (select p.name from t_plot p where p.id = t_building.plot_id)           as plotId,
       building_type,
       (select g.grid_name from t_grid g where g.id = t_building.grid_id)      as gridId,
       address_generation_method,
       address,
       building_number,
       unit_count,
       unit_household_count,
       (select count(h.id) from t_house h where h.building_id = t_building.id) as totalHouseholdCount
from t_building
where id = #{id}

子查询totalHouseholdCount放在了最后一行，这样会导致无法正确查询结果到实体类中

当子查询放在 SELECT 语句的最后一行时，ORM 框架可能无法直接将其结果映射到 t_building 实体对象的属性中，因为这样的查询结果并不符合 ORM 框架的预期结构。ORM 框架通常期望结果集中的每一行都映射到一个实体对象上。

正确SQL

将子查询totalHouseholdCount放在不是最后一行即可

select id,
       (select p.name from t_plot p where p.id = t_building.plot_id)           as plotId,
       building_type,
       (select g.grid_name from t_grid g where g.id = t_building.grid_id)      as gridId,
       (select count(h.id) from t_house h where h.building_id = t_building.id) as totalHouseholdCount,
       address_generation_method,
       address,
       building_number,
       unit_count,
       unit_household_count
from t_building
where id = #{id}