问题:想查询某个表A里面的数据关联的分表B数据,需要查询出A中每条数据中关联了B中多少条数据,还需要查出A关联表C中,每条A关联了多少条C中的数据。我用的是TP框架,当我直接用count()查询,关联两个表同时查询的时候会出现查询的数据量不正确的问题,具体原因和执行的顺序有关。最后采用的是DISTINCT函数查询,测试没有问题但是线上数据比较多就会出问题。所以优化了一下写法
原来写法:
SELECT
COUNT( DISTINCT C.sexp_id) AS count1,
COUNT( DISTINCT (B.id)) AS count2
FROM
`A`
LEFT JOIN C ON A.id = C.id
LEFT JOIN B ON A.e_id = C.id
ORDER BY
A.id DESC
LIMIT 0,
10
优化后
SELECT
t2.count2,
t1.count1
FROM
`A`
LEFT JOIN (SELECT id,COUNT(C.id) AS count2 FROM C group by id) t2 ON A.id = t2.id
LEFT JOIN (SELECT id,COUNT(B.id) AS count1 FROM B group by id) t1 ON A.id= t1.uid
ORDER BY
A.id DESC
LIMIT 0,
100