主表 鏈接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
```
uj5u.com熱心網友回復：
你看看下面的查詢和你的結果一樣嗎？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
uj5u.com熱心網友回復：
不知道你想要什么結果，看以下以下陳述句的注釋吧SELECT
count( 1 ) sum, -- 全部回傳的條數（你寫的）
count(r.strCode),-- r表參與的全部條數（你寫的）
count(distinct bv.主鍵或唯一索引) ,-- bv表參與查詢的條數（我加的）
count(distinct bc.主鍵或唯一索引) ,-- bc表參與查詢的條數（我加的）
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
uj5u.com熱心網友回復：
試下把count放到region部分：SELECT
count( 1 ) sum,
strCode_count,
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, count(a.strCode) strCode_count
from region a where a.strCode IN (
'420000',
'120115',
'120199'
) )r ON bv.strComeAdress = r.strCode
GROUP BY
bc.strDistrictCode
ORDER BY
bc.strDistrictCode
如果還不對，加上distinct:
SELECT
count(DISTINCT *) sum,
strCode_count,
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, count(a.strCode) strCode_count
from region a where a.strCode IN (
'420000',
'120115',
'120199'
) )r ON bv.strComeAdress = r.strCode
GROUP BY
bc.strDistrictCode
ORDER BY
bc.strDistrictCode
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/30030.html
標籤：MySQL