sql 查询问题

[挺热]

SELECT bizid AS bid, bizid, biz_name, biz_cid, biz_photo, biz_intro, (SELECT COUNT(*) FROM kacol_bizuser WHERE bizid=bid and want=1) AS count FROM kacol_bizinfo ORDER BY count DESC

在 mysql5.0 下正常 在mysql 4.0 下就不可以

[挺热]

那用什么方法可以解决嘛 ？

[faisun]

4.1 以上才支持子查询
你那个语句可以改为
left join 方法

SELECT i.*,count(u.*) as count

FROM kacol_bizinfo as i 

left join kacol_bizuser as u on u.bizid=bid and u.want=1


ORDER BY count DESC 

[挺热]

不行啊
count(u.*) as count
这里一加上去就出问题

[faisun]

4.0 以下不支持子查询

[faisun]

SELECT i.*,count(u.*) as `count`

FROM kacol_bizinfo as i 

left join kacol_bizuser as u on u.bizid=bid and u.want=1

ORDER BY count DESC 

[cuidenghong123]

join