在 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}