SQL Server：加入两个表和最后一列计数总计（按​​年份，zip）

Question

我有一个 housing 和我的SQL Server数据库中的 rimel_reports 表。我试图计算房屋邮政编码中犯罪的总出现，并带有一个查询。

这些表中有很多值，但是以下是我加入ZIP和Year的匹配的值（每个表之间的“唯一” ID都是完全不同的，以下只是一个示例）。

我的住房表示例数据：

UID	Zipcode	Saleyear	Soldprice
1	12345	2018 2018	300000
2	23345	2019	200000 200000
3	56777	2018 2018	500000
4	65789	2021	350000

表

犯罪	我的 MY	>
1	12345	2018
2	23345	2019
3	56777	2018
4	65789	2020

我想创建一个视图表（从单个查询），犯罪发生在那年发生的房屋邮政编码中计算犯罪：

UID	Zipcode	Saleyear Saleyear	Soldprice	犯罪发生在ZIP
1	12345	2018 2018	300000	23223345
2019	2019 200000	200000	200000	4
3 3 3 3	56777	2018	500000	50
4	65789	2021	350000	2

我尝试了以下内容，但这不是工作：

SELECT 
    h.uID, h.zipcode, h.saleyear, 
    COUNT(c.IncidentID) AS Crime Occurrences At Zip
FROM 
    Housing h
INNER JOIN 
    Crime_Reports c ON h.zipcode = c.zipcode
                    AND h.saleyear = c.occurredyear
GROUP BY 
    1
ORDER BY 
    1

编辑

测试并发布了下面的答案

Answer 1

更新答案

解决方案1 ​​多亏了@Charlieface（问题是使用1或1,2,3在该条款中）

 SELECT h.uID, h.zipcode, h.saleyear, COUNT(c.IncidentID) AS Crime Occurrences At Zip
   FROM Housing h
   INNER JOIN Crime_Reports c
            ON h.zipcode = c.zipcode
            AND h.saleyear = c.occurredyear
 GROUP BY h.uID, h.zipcode, h.saleyear
 ORDER BY h.uID, h.zipcode, h.saleyear

解决方案2 多亏了 @艾登或

WITH CrimesCTE AS (
    SELECT COUNT(IncidentID) AS CrimeOccurrencesAtZip, occurredyear, zipcode 
    FROM Crime_Reports
    GROUP BY occurredyear, zipcode
)
    
SELECT * 
FROM 
    (SELECT * FROM Housing) h
    LEFT JOIN 
    (SELECT CrimeOccurrencesAtZip, occurredyear, zipcode FROM CrimesCTE) c
        ON h.zipcode = c.zipcode 
        AND h.saleyear = c.occurredyear

Answer 2

我认为您可以在这种情况下使用常见的表格表达式（CTE）：

 With CTE AS (
    SELECT 
    COUNT(IncidentID) AS Crime Occurrences, 
    occurredyear, 
    zipcode 
    FROM Crime_Reports 
    GROUP BY occurredyear, zipcode)
    SELECT * 
    FROM ( 
        SELECT * FROM Housing
    ) tb1 
    LEFT JOIN (
        SELECT  
            Crime Occurrences,
            occurredyear, 
            zipcode 
        FROM CTE
   ) ON tb1.zipcode = tb2.zipcode 
        AND tb1.saleyear = tb2.occurredyear