sqlserver数据库"子连接"count数量查询

Question

car_report 是 车牌主表, plate_number 字典是car_reportd 的主键
也是 exceed_report表 和 associat_report表的外键

下面两个表 是单独查询各自的每台车的数量
select plate_number,COUNT(*) as 超速 from exceed_report group by plate_number

insert into exceed_report(plate_number)
values('粤c10090')
insert into exceed_report(plate_number)
values('粤c10060')
insert into exceed_report(plate_number)
values('粤c11662')
insert into exceed_report(plate_number)
values('粤C12353')

exceed_report 是超速表

select plate_number,COUNT(*) as 超载 from associat_report group by plate_number
associat_report 是超载表

insert into associat_report(plate_number)
values('粤c10090')
insert into associat_report(plate_number)
values('粤c10060')

------------------------------------------------------------------------------------------------
请问哪位老师sql语句能达到这样的效果啊？学生我 sql语句一般 求教.....
车牌 超速 超载
1 粤c10060 1 1
2 粤c10090 1 1
3 粤c11662 1 0
4 粤C12353 1 0

如何能达到这样的效果呢？ 求老师赐教 感谢

Answer 1

select a.plate_number 车牌,isnull(b.超速,0)超速,isnull(c.超载,0)超载 
from car_report a left join
(select plate_number,COUNT(*) as 超速 from exceed_report group by plate_number) b 
on a.plate_number=b.plate_number left join 
(select plate_number,COUNT(*) as 超载 from associat_report group by plate_number) c 
on a.plate_number=c.plate_number

Answer 2

select a.*,b.超载 from

(select plate_number,COUNT(*) as 超速 from exceed_report group by plate_number) a left join
(select plate_number,isnull(COUNT(*),0) as 超载 from associat_report group by plate_number) b on a.plate_number=b.plate_number