SQL Server 关联两表按年份、邮编统计对应区域犯罪发生总数

你的查询执行失败，存在三个明确问题：

• 带空格的列别名未做转义：SQL Server中包含空格的标识符必须用方括号[]包裹，直接写Crime Occurrences At Zip会触发语法解析错误。
• GROUP BY规则不符合要求：SQL Server默认开启全分组校验，SELECT子句中所有未被聚合函数包裹的字段，都必须加入GROUP BY列表，你仅按第一列uID分组，剩余的zipcode、saleyear、soldprice字段未加入分组，无法通过语法校验。
• 关联逻辑存在统计偏差风险：使用INNER JOIN直接关联两表后计数，会丢失“售出当年对应邮编无犯罪记录”的房屋数据；如果同邮编、同一年份存在多套房屋交易记录，JOIN生成的笛卡尔积还会导致犯罪次数重复统计，结果不准。

推荐先对犯罪表按「邮编+案发年份」维度预聚合统计犯罪总次数，再通过LEFT JOIN关联房屋表，这种写法既避免了重复计数的问题，也不会丢失房屋记录，数据量大时性能远高于直接JOIN后分组的写法，可直接用于创建视图：

CREATE VIEW View_Housing_Crime_Stats AS
SELECT 
    h.uID,
    h.zipcode,
    h.saleyear,
    h.soldprice,
    -- 无匹配犯罪记录时返回0，避免空值
    ISNULL(c.CrimeCount, 0) AS [Crime Occurrences At Zip]
FROM Housing h
LEFT JOIN (
    -- 预聚合：每个邮编每年的犯罪总数只算1次，避免关联后重复计数
    SELECT 
        zipcode,
        occurredyear,
        COUNT(IncidentID) AS CrimeCount
    FROM Crime_Reports
    GROUP BY zipcode, occurredyear
) c 
    ON h.zipcode = c.zipcode 
    AND h.saleyear = c.occurredyear

注：SQL Server 不推荐在视图定义中直接添加ORDER BY子句，如果需要按uID排序，建议在查询视图时额外加ORDER BY uID即可，减少不必要的性能开销。

如果你一定要用直接JOIN+分组的写法，需要补全GROUP BY字段、转义别名，同时把INNER JOIN改成LEFT JOIN避免数据丢失，但这种写法在数据量大时性能更差，不推荐：

-- 不推荐的写法，仅作语法修正参考
SELECT 
    h.uID,
    h.zipcode,
    h.saleyear,
    h.soldprice,
    COUNT(c.IncidentID) AS [Crime Occurrences At Zip]
FROM Housing h
LEFT JOIN Crime_Reports c 
    ON h.zipcode = c.zipcode
    AND h.saleyear = c.occurredyear
GROUP BY h.uID, h.zipcode, h.saleyear, h.soldprice
ORDER BY h.uID

只要Crime_Reports表中对应邮编、年份的案件数量和你给出的预期值一致，上述查询返回的字段格式、统计结果会完全匹配你的需求；如果某套房屋售出当年对应邮编没有犯罪记录，最后一列会返回0，且房屋记录不会被过滤。

内容的提问来源于stack exchange，提问作者John Jr.