select a.*, (select count(*) from b where a.id = b.id) bCount from a我执行这样的查询得花5秒 select a.*,xxx.bCount from a left join (select id,bCount from b group by b.id) xxx on a.id = xxx.id修改成这样得花14秒这样的语句该怎样优化呢,请教各位大牛a表2000多 b表9000多谢谢
select a.*,count(b.id) as bcount from a left join b on a.id=b.id
非常感谢,让我知道了还有这种写法但是实际上a表还跟一个42W的表(表c)join实际测试,发现你这种写法更慢非常感谢 select a.*,b.storename,sum(c.currcnt) as SumCount , (select count(newbillno) from mt_reinstore where newbillno = a.billno) as ReCount from MT_InStoreBill a left join MT_StoreCode b on b.storeID = a.storeid left join MT_InBillBarcodeLink c on c.billno = a.billno where a.toDealerID = 0 group by a.billno order by instoreDT desc MT_InStoreBill 2000多 MT_ReInStore 9000多 MT_InBillBarcodeLink 42W多
还有特别的是 MT_ReInStore 中 NewBillNo 不是主键 left join后,记录数会发生变化,不是想要的记录数
, (select count(newbillno) from mt_reinstore where newbillno = a.billno) as ReCount
from MT_InStoreBill a
left join MT_StoreCode b on b.storeID = a.storeid
left join MT_InBillBarcodeLink c on c.billno = a.billno
where a.toDealerID = 0 group by a.billno order by instoreDT desc MT_InStoreBill 2000多
MT_ReInStore 9000多
MT_InBillBarcodeLink 42W多
MT_ReInStore 中 NewBillNo 不是主键
left join后,记录数会发生变化,不是想要的记录数