我要进行3个表以上的查询,但是本来2表可以的语句用到3表或者以上就出错了
比如下面这样没问题
SELECT tb_a.id
,SUM(tb_b.score) AS sum_score
FROM tb_a LEFT JOIN tb_b ON tb_b.id = tb_a.id
WHERE tb_a.id REGEXP :id
GROUP BY tb_a.id
但是加上第3个表时查询到的数据不对
SELECT tb_a.id
,SUM(tb_b.score) AS sum_score
,COUNT(tb_c.pass) AS count_pass
FROM tb_a LEFT JOIN tb_b ON tb_b.id = tb_a.id
LEFT JOIN tb_c ON tb_c.id = tb_a.id
WHERE tb_a.id REGEXP :id
GROUP BY tb_a.id
请问这种多表的分组应该怎么做呢?
比如下面这样没问题
SELECT tb_a.id
,SUM(tb_b.score) AS sum_score
FROM tb_a LEFT JOIN tb_b ON tb_b.id = tb_a.id
WHERE tb_a.id REGEXP :id
GROUP BY tb_a.id
但是加上第3个表时查询到的数据不对
SELECT tb_a.id
,SUM(tb_b.score) AS sum_score
,COUNT(tb_c.pass) AS count_pass
FROM tb_a LEFT JOIN tb_b ON tb_b.id = tb_a.id
LEFT JOIN tb_c ON tb_c.id = tb_a.id
WHERE tb_a.id REGEXP :id
GROUP BY tb_a.id
请问这种多表的分组应该怎么做呢?
,SUM(tb_b.score) AS sum_score
,COUNT(tb_c.pass) AS count_pass
FROM (tb_a LEFT JOIN tb_b ON tb_b.id = tb_a.id)
LEFT JOIN tb_c ON tb_c.id = tb_a.id
WHERE tb_a.id REGEXP :id
GROUP BY tb_a.id
加上括号试试。
from (第一个结果集) A left join B on xxxx
SELECT tb_a.id
,SUM(tb_b.score) AS sum_score
,(SELECT COUNT(tb_c.pass)
FROM tb_c
WHERE tb_c.id = tb_a.id)AS count_pass
FROM tb_a LEFT JOIN tb_b ON tb_b.id = tb_a.id
WHERE tb_a.id REGEXP :id
GROUP BY tb_a.id
虽然可以不知道效率会不会比较低