Sql LEFT JOIN不显示计数0我期望的方式

SELECT
  Neighborhood,
  Category,
  COUNT(Neighborhood)
from A
INNER JOIN B
   ON (b.CrimeType = 
a.crimeType
  AND B.CrimeSubType = b.CrimeSubType
  AND a.year = 2024
GROUP BY
  Neighborhood,
  Category

SELECT
  Neighborhood,
  Category,
  COUNT(Neighborhood)
from A
LEFT JOIN B
   ON (b.CrimeType = a.crimeType
  AND B.CrimeSubType = b.CrimeSubType
  AND a.year = 2024
GROUP BY
  Neighborhood,
  Category

SELECT n.Neighborhood, c.Category, COUNT(b.CrimeType) AS Counts
FROM (SELECT DISTINCT Neighborhood FROM A) n
CROSS JOIN (SELECT DISTINCT Category FROM B) c
LEFT JOIN A a
    ON a.Neighborhood = n.Neighborhood AND
       a.year = 2024
LEFT JOIN B b
    ON B b.Category = c.Category AND
         b.CrimeType = a.CrimeType AND
         b.CrimeSubType = b.CrimeSubType
GROUP BY
    n.Neighborhood,
    c.Category
ORDER BY
    n.Neighborhood,
    c.Category;

Sql相关问答推荐

SUM(条件)在Oracle？

SQL(PostgreSQL)从条件创建点表

查找表中特定值的上次更新日期

从列中提取子字符串的ORDER BY CASE语句

查询页面推荐

如何找到一个组合的两个列，这是不是在其他表在ORACLE SQL？

每小时 Select 1行

我如何才能在付款人单列中拉出只有9个付款人单的人？

获取分布在同一行的列中的出现次数

SAS proc freq 或 proc sql 获取数据子集和整个数据的频率

用替代方案替换 SQL Cursor 以提高性能

具有多个表 JOINS 的 STRING_AGG 的替代方法 (SQL Server 2016)

递归 CTE 附加为行

Postgres存在限制问题「小值」

BigQuery Pivot 遗漏行

Set vs let vs 在snowflake中声明变量

SQL Select 最大并获取列名

如何对 SQL 表中的连续时间戳进行分组？

将单行中的多个行值转换为列

SQL中所有先前日期的累计总和