急急急！！！求高手指点，Sql server 根据某个字段查询两个表的总记录数？

Question

比如：
表A
Id UserId CompanyName
1 2 f
2 2 d
3 2 f
4 1 h
5 6 j
6 3 g
7 3 m
表 B
Id UserId PersnoalName
1 3 c
2 6 f
3 4 g
查询结果：
UserId 表A的数目 表B的数目 总数目
2 3 0 3
1 1 0 1
3 2 1 3
6 1 1 2
4 0 1 1
求高手指点。

Answer 1

好了，如下：我下班，要赶公司的班车啦。。

 

select 

t.UserID,

isnull(sum(Case t.tableName When 'A' Then 1 End),0) as '表A的数目',

isnull(sum(Case t.tableName When 'B' Then 1 End),0) as '表B的数目',

isnull(sum(1),0)

 from (

select UserID,CompanyName as [tName],'A' as tableName from A

union all

select UserID,PersonalName as [tName],'B' as tableName from B) t

Group By t.UserID

 

 

 

执行结果：

Answer 2

可以先将两个表分别分组求和，然後再合并
大致写法如下：
select userid,
sum(case when tb = 'A' then cnt else 0 end ) as 表A的数目
sum(case when tb = 'B' then cnt else 0 end ) as 表B的数目
sum(cnt) as 总数目
from
(select userid,count(*) as cnt,'A' as tb from A group by userid
union all
select userid,count(*) as cnt,'B' as tb from B group by userid) as T
group by userid

Answer 3

select isnull(a.id, b.id) as Userid,
isnull(a.count_A, 0) as 表A的数目,
isnull(b.count_B, 0) as 表B的数目,
isnull(a.count_A, 0) + isnull(b.count_B, 0) as 总数目
from
(select id, count(1) as count_A from A group by id ) a
full join
(select id, count(1) as count_B from B group by id ) b
on a.id=b.id