SELECT * FROM liuhuarecord lr
LEFT JOIN (SELECT sum(xxx) FROM cliprecord group by xxx) cr
ON cr.liuhuarecord_id = lr.idliuhuarecord 和 cliprecord 是一对多关系liuhuarecord 是一端
cliprecord 是多端
两个表的记录分别有3万条左右这个查询需要大概23秒 不知为何 请予神助
LEFT JOIN (SELECT sum(xxx) FROM cliprecord group by xxx) cr
ON cr.liuhuarecord_id = lr.idliuhuarecord 和 cliprecord 是一对多关系liuhuarecord 是一端
cliprecord 是多端
两个表的记录分别有3万条左右这个查询需要大概23秒 不知为何 请予神助
1 对 多 ?这不是 1对1了么
而且 SELECT sum(xxx) FROM cliprecord group by xxx 这一句有问题
LEFT JOIN (SELECT sum(xxx) FROM cliprecord group by xxx) cr
ON cr.liuhuarecord_id = lr.idcr里面根本不存在liuhuarecord_id,你这语句不出错的话就是做了笛卡儿乘积!
用下面的试试看:
SELECT * FROM liuhuarecord lr
LEFT JOIN (SELECT liuhuarecord_id,sum(xxx) FROM cliprecord group by xxx) cr
ON cr.liuhuarecord_id = lr.id
SELECT sum(xxx) FROM cliprecord group by liuhuarecord_id
这样没问题了吧 但执行时间还是很长
LEFT JOIN (SELECT liuhuarecord_id,sum(xxx) FROM cliprecord group by liuhuarecord_id) cr
ON cr.liuhuarecord_id = lr.id
没错 这句就是做了笛卡尔乘积 实际上子查询中我也是写了liuhuarecord_id了的,并且是有索引的,还是慢
结果虽然一样 但执行时间差十万八千里
LEFT JOIN cliprecord cr on lr.id = cr.liuhuarecord_id group by cr.liuhuarecord_id希望能帮到你