mysql的查询语句中的错误

原句为
select district, community, (select count(ID_Onsale) from onSale where community=c.community) from Community c
where community in (Select community from onSale where district=c.district group by community order by count(ID_Onsale) as b)
order by district;
报错limit和in不能一起用

解决方式1：使用伪表进行表连接（其中一个表没有id用不了）
解决方式2：
select district, community, (select count(ID_Onsale) from onSale where community=c.community) from Community c
where community in (Select b.community from(Select community from onSale where district=c.district group by community order by count(ID_Onsale) limit 5) as b)
order by district;
报错不认识最内层的c

应该怎么改

使用with t as (分组查询) 把子查询结果放临时表，然后连接临时表查询

可以将最内层的c改为c1或其他别名，避免与外层的c冲突，如下所示：

select district, community, (select count(ID_Onsale) from onSale where community=c.community) from Community c
where community in (Select b.community from(Select community, district from onSale where district=c.district group by community order by count(ID_Onsale) limit 5) as b)
order by district;