sql - 加入具有重复值的列时避免重复

Question

我正在尝试加入在我尝试加入的列中具有多个重复结果的表列。一个例子看起来有点像：

Month  | code | crime  
-----------------------
2017-1 | EO99 | Burglary
2017-1 | EO99 | Shoplifting
2017-2 | FO01 | Anti-Social Behaviour

正在加入的表如下所示：

Contents | F4  
-----------------------
EO99     | 4581  
EO98     | 10255 
FO01     | 4898  

期望的结果是：

Month  | code | crime                  | Population
---------------------------------------|------------
2017-1 | EO99 | Burglary               | 4581
2017-1 | EO99 | Shoplifting            | 4581
2017-2 | FO01 | Anti-Social Behaviour  | 4898

我正在尝试加入该代码列以添加包含人口数据的第四列。这是我当前的查询：

DROP TABLE LSOA_crimes_2017_joined;
SELECT DISTINCT
    crimes_data_GM_2017_2018.*, 
    ['2017_LSOA_Pop.'].F4 AS Population
INTO 
    LSOA_crimes_2017_joined
FROM 
    crimes_data_GM_2017_2018 AS data,
    ['2017_LSOA_Pop.']
INNER JOIN 
    crimes_data_GM_2017_2018 ON
    crimes_data_GM_2017_2018.[LSOA code] = 
    ['2017_LSOA_Pop.'].[Contents]
WHERE 
    crimes_data_GM_2017_2018.Month LIKE '2017%';

使用 Distinct 有助于避免它为每个结果所做的疯狂重复，但由于某些结果完全相同但犯罪实例不同，我需要两者都在新表中。

标签： sqltsql

Answer 1

您在 from 子句中有两个表，这正在创建笛卡尔积。您可能需要执行以下操作：

DROP TABLE LSOA_crimes_2017_joined;
SELECT DISTINCT
    crimes_data_GM_2017_2018.*, 
    ['2017_LSOA_Pop.'].F4 AS Population
INTO 
    LSOA_crimes_2017_joined
FROM 
    crimes_data_GM_2017_2018 AS data
INNER JOIN 
    ['2017_LSOA_Pop.'] ON
    crimes_data_GM_2017_2018.[LSOA code] = 
    ['2017_LSOA_Pop.'].[Contents]
WHERE 
    crimes_data_GM_2017_2018.Month LIKE '2017%';