select *,(select count(info_id) from info where bigid=a.bigid and cityId=@cityId and isclosed=0)as infoCount1 from BigClass a where location=@location order by displayindex asc
查询分类时同时查询此分类有多少条信息。
即查询大类表的所有同时,再去查询info 表属于每个大类的信息数量 count(info_id)查询出的结果是
bigid bigname infoCount1
1 大类别 57以上的SQL语句似乎效率很慢。谁帮我改良下。分不够我再加
查询分类时同时查询此分类有多少条信息。
即查询大类表的所有同时,再去查询info 表属于每个大类的信息数量 count(info_id)查询出的结果是
bigid bigname infoCount1
1 大类别 57以上的SQL语句似乎效率很慢。谁帮我改良下。分不够我再加
left join info b on a.bigid=b.bigid
where b.cityId=@cityId and b.isclosed=0 and a.location=@location
group by a.bigid,a.bigname
select
a.bigid,a.bigname,count(1) as infoCount1
from
BigClass a
left join info b on a.bigid=b.bigid
where
b.cityId=@cityId and b.isclosed=0 and a.location=@location
group by
a.bigid,a.bigname
(select a.bigid,a.bigname,count(1) as infoCount1 from BigClass a
left join info b on a.bigid=b.bigid
where b.cityId=@cityId and b.isclosed=0 and a.location=@location
group by a.bigid,a.bigname) b
on a.bigid=b.bigid