sql中分组和多表联结

上图为表1

表2

备注：表1的BID与表2的BID是主外键关系

我要查询出所有的BID出现的次数并联结表2把那ID变成名称，结果如下：

名称 (Title) 次数

人生若只如初见 2

入学那天遇见你 2

感谢折磨你的人 5

select title,isnull(BIDCount,0) from T2 left join

(select count(BID) as BIDCount,BID from T1 group by BID) as T

on T.BID = T2.BID

select title as 名称,次数=(select count(borrowid) from 表1 where 表1.bid=表2.bid) from 表2

select title as 名称, BID as 次数 from 表1,表2 where 表1.bid=表2.bid group by bid,title