高分请问一下，急急急！！！求高手指点，Sql server 根据某个字段查询两个表的总记录数？？
比如：表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
求高手指点。
急急急！！！求高手指点，Sql server 根据某个字段查询两个表的总记录数？
表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
求高手指点。
最新回答
素觞流年
2024-10-12 11:47:20
好了，如下：我下班，要赶公司的班车啦。。
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
执行结果：
梅芳竹清
2024-10-12 11:30:58
大致写法如下：
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
野稚
2024-10-12 14:46:10
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
热门标签
