SQL-从另一个表中获取一列以加入此查询

Question

我下面有显示location_id反社会犯罪总数的代码，但我也希望location_name从另一个称为location_dimbe
output的表中获取代码。我试图找到一种解决方法，UNION但无法使其正常工作。有任何想法吗？

SELECT fk5_location_id , COUNT(fk3_crime_id) as TOTAL_ANTISOCIAL_CRIMES
from CRIME_FACT
WHERE fk1_time_id = 3 AND fk3_crime_id = 1 
GROUP BY fk5_location_id;

Answer 1

您要用来join查找位置名称。该查询可能看起来像这样：

SELECT ld.location_name, COUNT(cf.fk3_crime_id) as TOTAL_ANTISOCIAL_CRIMES
from CRIME_FACT cf join
     LOCATION_DIM ld
     on cf.fk5_location_id = ld.location_id
WHERE cf.fk1_time_id = 3 AND cf.fk3_crime_id = 1
GROUP BY ld.location_name;

您需要为ld.location_name和输入正确的列名称ld.location_id。