有两张表
1：借出书籍表 borrowing（无主键）
其中列名： book_name,reader_name
假设数据为：book_name reader_name
sql zzq
sql hhy
server zzq
2: 归还书表 return（无主键）
其中列名： book_name,reader_name
假设数据为：book_name reader_name
server hhy
vc zzq
现在我想得到一个结果集，有四列：书名（两张表中所涉及的）
borrowing中的出现次数
return中的出现次数
两张表中的总出现次数
既
书名 borrowing中的出现次数 return中的出现次数 两张表中的总出现次数
sql 2 0 2
server 1 1 2
vc 0 1 1
请问SQL 语句该如何写？
---------------------------------------------------------------
select b.book_name ,(select Count(c.book_name) from borrowing c where c.book_name=b.book_name) as borTimes
,(select Count(d.book_name) from return1 d where d.book_name=b.book_name) as returnTimes,count(b.book_name) as totalTimes
from
(select * from borrowing union select * from return1 ) b group by b.book_name
---------------------------------------------------------------
select T.book_name,
sum(case flag when 'b' then num else 0 end) as borrowings,
sum(case flag when 'r' then num else 0 end) as returns,
sum(num) as totals from
(select book_name,reader_name,'b', as flag, 1 as num from borrowing
union select book_name,reader_name,'r' as flag, 1 as num from return) as T
group by T.book_name