sql 多表联查 导致数据重复

Question

主表 链接community region
但是community和region并没有关联 所以主表一条数据跟这两个表命中两次导致查询count数不对, 求大神们提供一些别的写法

SELECT
    count( 1 ) sum,
    count(r.strCode),
    bc.strDistrict,
    bv.strCommunityGuid,
    bc.strDistrictCode,
    bv.strComeAdress
FROM
    visitor bv
    LEFT JOIN community bc ON bv.strCommunityGuid = bc.strCommunityCode
    LEFT JOIN (select a.strCode from region a where a.strCode IN (
        '420000',
        '120115',
        '120199'
    ) )r ON bv.strComeAdress = r.strCode
GROUP BY
    bc.strDistrictCode 
ORDER BY
    bc.strDistrictCode

Answer 1

你要分组，那么查询多少个字段，分组那里就要全加上，同时字段有一个不同就不算同一组

SELECT count( 1 ) sum, count( r.strCode ), bc.strDistrict, bv.strCommunityGuid, bc.strDistrictCode, bv.strComeAdress
FROM visitor bv
LEFT JOIN community bc ON bv.strCommunityGuid = bc.strCommunityCode
LEFT JOIN (
SELECT a.strCode FROM region a WHERE a.strCode IN ( '420000', '120115', '120199' )
) r ON bv.strComeAdress = r.strCode
GROUP BY bc.strDistrict, bv.strCommunityGuid, bc.strDistrictCode, bv.strComeAdress
ORDER BY bc.strDistrictCode