我有一个连接到县表的查询。该表包含14个不同的国家。我正在计算每个县在一段时间内的成员数量。当我选择的时间跨度不包括来自某个县的任何成员时,无论我对县表使用右连接还是内连接,都不会显示该县。我的目标是,如果一个县没有返回任何成员,则显示该县的总数为0。我该怎么做呢?
SELECT DISTINCT
e.County,
COUNT(DISTINCT d.MemberID) AS TotalUniqueProviders
FROM
dw.FactMedicalClaimLine a
INNER JOIN
dw.DimMember d
ON
a.MemberKey = d.MemberKey
RIGHT JOIN
dw.DimGeography e
ON
a.GeographyKey = e.GeographyKey
WHERE
LEFT(ServiceDate, 6) >= 201001
AND LEFT(PaidDate, 6) BETWEEN 201010 AND 201012
将过滤器移动到JOIN
子句
RIGHT JOIN
dw.DimGeography e
ON
a.GeographyKey = e.GeographyKey
AND
LEFT(ServiceDate, 6) >= 201001
AND LEFT(PaidDate, 6) BETWEEN 201010 AND 201012
由于OUTER JOIN
而保留的国家行将为ServiceDate
和PaidDate
提供NULL
,因此最终被WHERE
条款排除在外。
您似乎也在发布的查询版本中缺少GROUP BY e.GeographyKey
,您不需要DISTINCT
与GROUP BY
。
将WHERE
替换为AND
,即:
ON a.GeographyKey = e.GeographyKey
AND LEFT(ServiceDate, 6) >= 201001
AND LEFT(PaidDate, 6) BETWEEN 201010 AND 201012