select s.*,count(p.intelligent_spaces_id) as subSpaceNum from bv_intelligent_spaces s
left join bv_intelligent_spaces p on s.intelligent_spaces_id = p.intelligent_spaces_parent_id
where s.intelligent_spaces_parent_id = '0' GROUP BY s.intelligent_spaces_id;
select s.*,count(r.intelligent_spaces_id) as deviceNum from bv_intelligent_spaces s
left join bv_intelligent_spaces_resource r on s.intelligent_spaces_id = r.intelligent_spaces_id
where s.intelligent_spaces_parent_id = '0' GROUP BY s.intelligent_spaces_id;
上面的兩個SQL查詢得到的count值是對的，但是按照下面的方式合起來拿到的count值就不對了，求解！！！！！
select s.*,
count(r.intelligent_spaces_id) as deviceNum,
count(p.intelligent_spaces_id) as subSpaceNum
from bv_intelligent_spaces s
left join bv_intelligent_spaces_resource r on s.intelligent_spaces_id = r.intelligent_spaces_id
left join bv_intelligent_spaces p on s.intelligent_spaces_id = p.intelligent_spaces_parent_id
where s.intelligent_spaces_parent_id = '0' GROUP BY s.intelligent_spaces_id;
uj5u.com熱心網友回復：
你這樣寫回傳的count值時是都是最大的count值吧，原因是因為你在關聯一張表時，滿足這個關聯條件的資料記錄條數已經確定了，你再拿同樣的關聯條件去關聯另外一張表，滿足條件的資料肯定也有那么多條，舉個例子 a表有1條資料，b表有7條資料，c有1條資料a關聯b的結果就是7條，你再去關聯c 回傳的結果是左表的全資料的記錄數，所以不管怎么怎么關聯回傳的都是記錄數多的那個統計值
你可以考慮一下用union（多條記錄） 或者兩個子查詢關聯（一條記錄） 用子查詢大致就是
select a.*,a.countnum,b.countnum
from(你寫的第一個陳述句) a
left join
(你寫的第二個陳述句) b on a.intelligent_spaces_id = b.intelligent_spaces_id
用union就是
你寫的第一條
union
你寫的第二條
uj5u.com熱心網友回復：
用子查詢吧, LEFT JOIN寫多了很可能出問題的, 比如有的表之間是多對多的關系, 你會查到重復的記錄, 笛卡爾積.uj5u.com熱心網友回復：
select s.*,
count(p.intelligent_spaces_id) as subSpaceNum from (
select s.*,
count(r.intelligent_spaces_id) as deviceNum
from bv_intelligent_spaces s
left join bv_intelligent_spaces_resource r on s.intelligent_spaces_id =
r.intelligent_spaces_id
where s.intelligent_spaces_parent_id = '0'
GROUP BY s.intelligent_spaces_id
) s
left join bv_intelligent_spaces p on s.intelligent_spaces_id =
p.intelligent_spaces_parent_id
where s.intelligent_spaces_parent_id = '0'
GROUP BY s.intelligent_spaces_id;
;
uj5u.com熱心網友回復：
大佬，太強了，這個可以！！！！！轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/71416.html
標籤：Java相關