Select s. *, count (p.i ntelligent_spaces_id) as subSpaceNum from bv_intelligent_spaces s
Left the join bv_intelligent_spaces p on s.i ntelligent_spaces_id=p.i ntelligent_spaces_parent_id
Where s.i ntelligent_spaces_parent_id='0' GROUP BY s.i ntelligent_spaces_id;

Select s. *, count (r.i ntelligent_spaces_id) as deviceNum from bv_intelligent_spaces s
Left the join bv_intelligent_spaces_resource r on s.i ntelligent_spaces_id=r.i ntelligent_spaces_id
Where s.i ntelligent_spaces_parent_id='0' GROUP BY s.i ntelligent_spaces_id;

The above two SQL queries to get the count value is right, but according to the following way together to get the count value is wrong, solving!!!!!!!!!!!
Select s. *,
Count (r.i ntelligent_spaces_id) as deviceNum,
Count (p.i ntelligent_spaces_id) as subSpaceNum
The from bv_intelligent_spaces s
Left the join bv_intelligent_spaces_resource r on s.i ntelligent_spaces_id=r.i ntelligent_spaces_id
Left the join bv_intelligent_spaces p on s.i ntelligent_spaces_id=p.i ntelligent_spaces_parent_id
Where s.i ntelligent_spaces_parent_id='0' GROUP BY s.i ntelligent_spaces_id;

CodePudding user response:

When you wrote the count value of the return is one of the largest count value is right, because you are in a list associated, satisfy the condition on the number of data records have been identified, you get the same association condition to another table, associated data must meet the conditions there are so many, for example, a table has only one data has 7 b table data, have 1 c data
Associated b is the result of the 7, you go to associated c returns as a result of all the data on the number of records in the left panel, so no matter what how to link to return to the one that is the record number statistics
You can consider to use the union (multiple records) or two sub queries (a record) associated with the subquery is roughly
Select a. *, a.c ountnum, biggest ountnum
The from (the first statement) you wrote a
Left the join
(the second statement you wrote) b on Anderson ntelligent_spaces_id=b.i ntelligent_spaces_id

With the union is
You write the first article
The union
The second you wrote

CodePudding user response:

Use a subquery, LEFT to JOIN more likely write a problem, such as some is a many-to-many relationship between tables, you will find duplicate records, cartesian product.

CodePudding user response:

 select s. *, 
Count (p.i ntelligent_spaces_id) as subSpaceNum from (
Select s. *, 
Count (r.i ntelligent_spaces_id) as deviceNum 
The from bv_intelligent_spaces s 
Left the join bv_intelligent_spaces_resource r on s.i ntelligent_spaces_id=
R.i ntelligent_spaces_id 
Where s.i ntelligent_spaces_parent_id='0' 
GROUP BY s.i ntelligent_spaces_id 
) s 
Left the join bv_intelligent_spaces p on s.i ntelligent_spaces_id=
P.i ntelligent_spaces_parent_id 
Where s.i ntelligent_spaces_parent_id='0' 
GROUP BY s.i ntelligent_spaces_id; 
; 

CodePudding user response:

The

reference 3 floor nayi_224 response:

 select s. *, 
Count (p.i ntelligent_spaces_id) as subSpaceNum from (
Select s. *, 
Count (r.i ntelligent_spaces_id) as deviceNum 
The from bv_intelligent_spaces s 
Left the join bv_intelligent_spaces_resource r on s.i ntelligent_spaces_id=
R.i ntelligent_spaces_id 
Where s.i ntelligent_spaces_parent_id='0' 
GROUP BY s.i ntelligent_spaces_id 
) s 
Left the join bv_intelligent_spaces p on s.i ntelligent_spaces_id=
P.i ntelligent_spaces_parent_id 
Where s.i ntelligent_spaces_parent_id='0' 
GROUP BY s.i ntelligent_spaces_id; 
; 

, is too strong, this can be!!!!!!!!!!!