SQl 多表 统计
数据库中有很多表，我要统计 每个编辑的录入条数 列Inputer 记录编辑名
例如:
产品表 Product
PId Inputer
1 a
2 a
3 b
4 c
下载表 DownLoad
Did Inputer
1 b
2 a
3 b
4 a
用户表 Users
Uid UserName
1 a
2 b
3 c
我有如下语句可以统计
SELECT U.UserName
DCount = (SELECT Count(1) FROM DownLoad D WHERE D.Inputer = U.UserName),
PCount = (SELECT Count(1) FROM Product P WHERE P.Inputer = U.UserName),
FROM Users U
可以得到如下内容
UerName Dcount Pcount
a 2 2
b 2 1
c 0 1
问题 我想进一步扩展功能得到横竖的总计，类似如下查询结果
UerName Dcount Pcount Total
a 2 2 4
b 2 1 3
c 0 1 1
Total 4 4 8
这样的SQL 怎么写？？
[解决办法]
SELECT U.UserName,
DCount = isnull(D.cnt,0),
PCount = isnull(P.cnt,0),
Total = isnull(D.cnt,0)+isnull(P.cnt,0)
FROM Users U left join (SELECT Inputer,cnt=Count(1) FROM DownLoad group by Inputer) D on U.UserName = D.Inputer
left join (SELECT Inputer,cnt=Count(1) FROM Product group by Inputer) P on U.UserName = p.Inputer
union
SELECT UserName= 'Total ',
DCount = sum(isnull(D.cnt,0)),
PCount = sum(isnull(P.cnt,0)),
Total = sum(isnull(D.cnt,0)+isnull(P.cnt,0))
FROM Users U left join (SELECT Inputer,cnt=Count(1) FROM DownLoad group by Inputer) D on U.UserName = D.Inputer
left join (SELECT Inputer,cnt=Count(1) FROM Product group by Inputer) P on U.UserName = p.Inputer
[解决办法]
create view view1
as
(
SELECT U.UserName
DCount = (SELECT Count(1) FROM DownLoad D WHERE D.Inputer = U.UserName),
PCount = (SELECT Count(1) FROM Product P WHERE P.Inputer = U.UserName),
FROM Users U
)
go
select UerName , Dcount , Pcount , Total=Dcount+Pcount
from
view1
union all
select 'Total ' as UerName , sum(Dcount) , sum(Pcount) , sum(Dcount)+sum(Pcount)
from
view1
