select count(db.id) as sell_count,db.borough_id from housesell as db where db.borough_id = a.id => select count(db.id) as sell_count,db.borough_id from housesell as db,borough as a where db.borough_id = a.id试一试,没这么弄过
select count(id) as sell_count,borough_id from housesell where housesell.borough_id = a.id 或者 select count(id) as sell_count,borough_id from housesell where housesell.borough_id = borough.id 试试呢
这个多表查询里,不光只有这两个表,还有其他的left join..
这个多表查询里,不光只有这两个表,还有其他的left join..
select a.*, d.sell_count from borough as a left join ( select da.* from ( select count(db.id) as sell_count,db.borough_id from housesell as db,borough as e where db.borough_id = e.id ) as da ) as d where a.is_checked = 1 order by id desc LIMIT 0, 10
SELECT a.*, COUNT(db.id) AS sell_count FROM borough AS a LEFT JOIN housesell AS db ON( db.borough_id = a.id) WHERE a.is_checked = 1 ORDER BY a.id ORDER BY id DESC LIMIT 0, 10我觉得没必要写得那么繁琐,你无非是想知道每个记录对应了另一张表中的多少记录,试试我这个sql看看,效率比你那个肯定要高
SELECT a.*, COUNT(db.id) AS sell_count FROM borough AS a LEFT JOIN housesell AS db ON( db.borough_id = a.id) WHERE a.is_checked = 1 ORDER BY a.id ORDER BY id DESC LIMIT 0, 10没贴上
把ORDER BY a.id 改成group bySELECT a.*, COUNT(db.id) AS sell_count FROM borough AS a LEFT JOIN housesell AS db ON( db.borough_id = a.id) WHERE a.is_checked = 1 GROUP BY a.id ORDER BY id DESC LIMIT 0, 10
=>
select count(db.id) as sell_count,db.borough_id from housesell as db,borough as a where db.borough_id = a.id试一试,没这么弄过
这个多表查询里,不光只有这两个表,还有其他的left join
2 borough_name varchar(100) gbk_chinese_ci 否 无 修改 删除 更多
3 borough_letter varchar(10) gbk_chinese_ci 是 NULL 修改 删除 更多
4 borough_alias varchar(100) gbk_chinese_ci 是 NULL 修改 删除 更多
5 cityarea_id tinyint(4) 是 0 修改 删除 更多
6 borough_section tinyint(4) 是 0 修改 删除 更多
7 borough_address varchar(250) gbk_chinese_ci 是 NULL 修改 删除 更多
8 borough_type tinyint(4) 是 0 修改 删除 更多
9 elementary_school varchar(40) gbk_chinese_ci 是 NULL 修改 删除 更多
10 middle_school varchar(40) gbk_chinese_ci 是 NULL 修改 删除 更多
11 borough_thumb varchar(100) gbk_chinese_ci 是 NULL 修改 删除 更多
12 sell_num int(11) 是 0 修改 删除 更多
13 rent_num int(11) 是 0 修改 删除 更多
14 sell_time varchar(10) gbk_chinese_ci 是 NULL 修改 删除 更多
15 borough_avgprice int(11) 是 0 修改 删除 更多
16 borough_evaluate int(10) UNSIGNED 是 NULL 修改 删除 更多
17 evaluate_time int(11) 否 无 修改 删除 更多
18 percent_change float 是 0 修改 删除 更多
19 layout_drawing int(11) 是 0 修改 删除 更多
20 layout_picture int(11) 是 0 修改 删除 更多
21 layout_map varchar(50) gbk_chinese_ci 是 NULL 修改 删除 更多
22 click_num int(11) 是 0 修改 删除 更多
23 is_checked tinyint(4) 否 0 修改 删除 更多
24 creater varchar(20) gbk_chinese_ci 是 NULL 修改 删除 更多
25 borough_developer_id int(11) 是 0 修改 删除 更多
housesell 表 id int(11) 否 无 AUTO_INCREMENT 修改 删除 更多
2 house_title varchar(40) gbk_chinese_ci 否 无 修改 删除 更多
3 cityarea_id tinyint(4) 是 0 修改 删除 更多
4 house_type tinyint(4) 否 无 修改 删除 更多
5 house_no varchar(20) gbk_chinese_ci 是 NULL 修改 删除 更多
6 house_price float 否 无 修改 删除 更多
7 house_totalarea float 是 0 修改 删除 更多
8 house_room tinyint(4) 是 0 修改 删除 更多
9 house_hall tinyint(4) 是 0 修改 删除 更多
10 house_toilet tinyint(4) 是 0 修改 删除 更多
11 house_veranda tinyint(4) 是 0 修改 删除 更多
12 house_thumb varchar(100) gbk_chinese_ci 是 NULL 修改 删除 更多
13 house_topfloor tinyint(4) 是 0 修改 删除 更多
14 house_floor tinyint(4) 是 0 修改 删除 更多
15 house_age varchar(100) gbk_chinese_ci 是 NULL 修改 删除 更多
16 house_toward tinyint(4) 是 0 修改 删除 更多
17 house_fitment tinyint(4) 是 0 修改 删除 更多
18 house_feature varchar(250) gbk_chinese_ci 是 NULL 修改 删除 更多
19 house_desc mediumtext gbk_chinese_ci 是 NULL 修改 删除 更多
20 borough_id int(11) 是 0 修改 删除 更多
或者
select count(id) as sell_count,borough_id from housesell where housesell.borough_id = borough.id
试试呢
这个多表查询里,不光只有这两个表,还有其他的left join..
select
a.*, d.sell_count
from
borough as a
left join (
select da.* from (
select count(db.id) as sell_count,db.borough_id from housesell as db,borough as e
where db.borough_id = e.id
) as da
) as d
where
a.is_checked = 1
order by id desc
LIMIT 0, 10
SELECT a.*, COUNT(db.id) AS sell_count FROM borough AS a
LEFT JOIN housesell AS db ON( db.borough_id = a.id)
WHERE
a.is_checked = 1
ORDER BY a.id
ORDER BY id DESC
LIMIT 0, 10我觉得没必要写得那么繁琐,你无非是想知道每个记录对应了另一张表中的多少记录,试试我这个sql看看,效率比你那个肯定要高
LEFT JOIN housesell AS db ON( db.borough_id = a.id)
WHERE
a.is_checked = 1
ORDER BY a.id
ORDER BY id DESC
LIMIT 0, 10没贴上
LEFT JOIN housesell AS db ON( db.borough_id = a.id)
WHERE
a.is_checked = 1
GROUP BY a.id
ORDER BY id DESC
LIMIT 0, 10