SELECT a.id, IFNULL(b.num, 0) AS num FROM a 
LEFT JOIN (SELECT id, COUNT(*) AS num FROM b GROUP BY id) b on b.id=a.id

測試結果:

declare @t table
(sn int,name varchar(10),category varchar(20),amt int)
declare @y table
(sn int primary key,name varchar(10),category varchar(20),amt int)

insert into @t values (1,'Liu','BCar',1),(1,'Chiu','forcement',3000),(4,'Liu','Enforcement',2500)
insert into @y values (1,'Liu','ACar',1),(2,'Chiu','Enforcement',3000),(3,'Wang','Car',4000),(4,'Liu','Enforcement',2500)


select b.sn,b.name ,count(a.sn)
from @t a right join @y b on a.sn = b.sn
group by a.sn,b.sn,b.name

ans:
sn name _count
1 Liu 2
2 Chiu 0
3 Wang 0
4 Liu 1

select count(1),b.id from b where b.id in (select id from a where /可以查詢出25.26.27.28的條件/) group by b.id