SQL table more LianZha leads to data duplication-CodePudding

The main table link community region
But is no connection between the community and the region and so the main table with both a data table hit twice to query the count number is wrong, for the great god provide some other writing
` ` `
SELECT
Count (1) the sum,
Count (r.s trCode),
The BC strDistrict,
Bv strCommunityGuid,
The BC strDistrictCode,
Bv. StrComeAdress
The FROM
The visitor bv
LEFT the JOIN community BC ON bv. StrCommunityGuid=BC. StrCommunityCode
LEFT the JOIN (select a.s trCode from region where a a.s trCode IN (
'420000',
'120115',
'120199'
)) r ON bv. StrComeAdress=r.s trCode
GROUP BY
BC. StrDistrictCode
The ORDER BY
BC. StrDistrictCode

` ` `

CodePudding user response:

You look at the following query as well as your results?

 SELECT 
Count (1) the sum, 
Count (r.s trCode), 
The BC strDistrict, 
Bv strCommunityGuid, 
The BC strDistrictCode, 
Bv. StrComeAdress 
The FROM 
The visitor bv 
LEFT the JOIN community BC ON bv. StrCommunityGuid=BC. StrCommunityCode 
LEFT the JOIN (select a.s trCode from region where a a.s trCode IN (
'420000', 
'120115', 
'120199' 
)) r ON bv. StrComeAdress=r.s trCode 
GROUP BY 
The BC strDistrict, 
Bv strCommunityGuid, 
The BC strDistrictCode, 
Bv. StrComeAdress 
The ORDER BY 
BC. StrDistrictCode

CodePudding user response:

Don't know what do you want the results, see the following comments of the following statements
SELECT
Count (1) the sum of all - returns the number () you wrote
Count (r.s trCode) - r table to participate in all the article number (you write)
Count (distinct bv. The primary key or unique index), the article - bv table in the query number (I)
Count (distinct BC. The primary key or unique index), the article - BC table in the query number (I)
The BC strDistrict,
Bv strCommunityGuid,
The BC strDistrictCode,
Bv. StrComeAdress
The FROM
The visitor bv
LEFT the JOIN community BC ON bv. StrCommunityGuid=BC. StrCommunityCode
LEFT the JOIN (select a.s trCode from region where a a.s trCode IN (
'420000',
'120115',
'120199'
)) r ON bv. StrComeAdress=r.s trCode
GROUP BY
BC. StrDistrictCode
The ORDER BY
BC. StrDistrictCode

CodePudding user response:

Try putting the count in part region:

 SELECT 
Count (1) the sum, 
StrCode_count, 
The BC strDistrict, 
Bv strCommunityGuid, 
The BC strDistrictCode, 
Bv. StrComeAdress 
The FROM 
The visitor bv 
LEFT the JOIN community BC ON bv. StrCommunityGuid=BC. StrCommunityCode 
LEFT the JOIN (select a.s trCode, count (a.s trCode) strCode_count 
The from region where a a.s trCode IN (
'420000', 
'120115', 
'120199' 
)) r ON bv. StrComeAdress=r.s trCode 
GROUP BY 
BC. StrDistrictCode 
The ORDER BY 
BC. StrDistrictCode

If it is wrong, with distinct:

 SELECT 
Count (DISTINCT *) sum, 
StrCode_count, 
The BC strDistrict, 
Bv strCommunityGuid, 
The BC strDistrictCode, 
Bv. StrComeAdress 
The FROM 
The visitor bv 
LEFT the JOIN community BC ON bv. StrCommunityGuid=BC. StrCommunityCode 
LEFT the JOIN (select a.s trCode, count (a.s trCode) strCode_count 
The from region where a a.s trCode IN (
'420000', 
'120115', 
'120199' 
)) r ON bv. StrComeAdress=r.s trCode 
GROUP BY 
BC. StrDistrictCode 
The ORDER BY 
BC. StrDistrictCode

refer to the original poster IIXL response:

the main table link community region
But is no connection between the community and the region and so the main table with both a data table hit twice to query the count number is wrong, for the great god provide some other writing
` ` `
SELECT
Count (1) the sum,
Count (r.s trCode),
The BC strDistrict,
Bv strCommunityGuid,
The BC strDistrictCode,
Bv. StrComeAdress
The FROM
The visitor bv
LEFT the JOIN community BC ON bv. StrCommunityGuid=BC. StrCommunityCode
LEFT the JOIN (select a.s trCode from region where a a.s trCode IN (
'420000',
'120115',
'120199'
)) r ON bv. StrComeAdress=r.s trCode
GROUP BY
BC. StrDistrictCode
The ORDER BY
BC. StrDistrictCode

` ` `