SQL 小白提问~弱弱求大神

[collmeaaa]

有三个数据表
1.表名： Nature

Number Nature
1 Global
2 Local
3 Regional


2.表名 Type
Number Type
1 Closed
2 on hold
3 on Progress
4. not start


3.表名 Summary
ID Nature Type
1 Local on hold
2 Regional close
........


现在我想看每个Type和Nature的Summary有几个
那一共就是3*4=12个状态
那应该怎么统计呢， 如果没有就是0
结果类似于
GLOBAL CLOSED 1
LOCAL CLOSED 0
Regional closed 2
.......

[oraclecaicai]

SELECT
       n.Nature
,      t.Type
,      COUNT(s.ID)
FROM      Nature  n
JOIN      Type    t ON 1 = 1
LEFT JOIN Summary s ON ( n.Nature = s.Nature AND t.Type = s.Type )
GROUP BY n.Nature, t.Type

[还在加载中灬]

SELECT T1.Nature,T2.[Type],COUNT(T3.ID)
FROM Nature T1 CROSS JOIN [Type] T2
	LEFT JOIN Summary T3 ON T1.Nature=T3.Nature AND T2.[Type]=T3.[Type]
GROUP BY T1.Nature,T2.[Type]