我正在使用SQL Server 2008。
我有一个需要稍微调整的查询。当我计算与网站相关联的品牌时,它计算得很好,但是当我试图计算与品牌相关联的模型时,网站的数量发生了变化。
表
SITES
SiteID // pk
SiteName
BRANDS
BrandID // pk
SiteID // fk
Brand
MODELS
ModelID // pk
BrandID // fk
这个查询产生与站点相关联的TotalBrands的正确数量:
SELECT S.SiteID,
S.SiteName,
COUNT(B.BrandID) AS TotalBrands
FROM Sites S
LEFT JOIN Brands B
ON S.SiteID = B.SiteID
GROUP BY S.SiteID, S.SiteName
当我在查询中添加更多的内容时,TotalBrands是不准确的。
SELECT S.SiteID,
S.SiteName,
COUNT(B.BrandID) AS TotalBrands,
COUNT(M.ModelID) AS TotalModels
FROM Sites S
LEFT JOIN Brands B
ON S.SiteID = B.SiteID
LEFT JOIN Models M
ON B.BrandID = M.BrandID
GROUP BY S.SiteID, S.SiteName
我想包括所有的网站,无论他们是否有品牌。我想数一下所有的品牌,不管它们有没有型号。我要计算所有的模型。
我的查询有什么问题?
正如评论中所说,您正在将最后一个JOIN
的结果相乘。因此,您要么先进行DISTINCT
计数,要么先进行分组。
SELECT S.SiteID,
S.SiteName,
COUNT(B.BrandID) AS TotalBrands,
SUM(M.Models) AS TotalModels
FROM Sites S
LEFT JOIN Brands B
ON S.SiteID = B.SiteID
LEFT JOIN ( SELECT BrandId, COUNT(*) Models
FROM Models
GROUP BY BrandId) M
ON B.BrandID = M.BrandID
GROUP BY S.SiteID, S.SiteName
或
SELECT S.SiteID,
S.SiteName,
COUNT(DISTINCT B.BrandID) AS TotalBrands,
COUNT(M.ModelId) AS TotalModels
FROM Sites S
LEFT JOIN Brands B
ON S.SiteID = B.SiteID
LEFT JOIN Models M
ON B.BrandID = M.BrandID
GROUP BY S.SiteID, S.SiteName