22,300
社区成员




create table users
(id int,name varchar(10))
insert into users
select 1,'张三' union all
select 2,'李四'
create table dataA
(userid int,contentA varchar(10))
insert into dataA
select 1,'XXX' union all
select 2,'XXX'
create table dataB
(userid int,contentB varchar(10))
insert into dataB
select 1,'XXX' union all
select 1,'XXX' union all
select 2,'XXX'
select a.name 'userid',
isnull(b.qty,0) 'dataAnum',
isnull(c.qty,0) 'dataBnum'
from users a
left join
(select userid,count(1) 'qty'
from dataA
group by userid) b on a.id=b.userid
left join
(select userid,count(1) 'qty'
from dataB
group by userid) c on a.id=c.userid
/*
userid dataAnum dataBnum
---------- ----------- -----------
张三 1 2
李四 1 1
(2 row(s) affected)
*/
create table [user](id int,name varchar(10))
insert into [user]
select 1 ,'张三' union all
select 2 ,'李四'
create table dataA(userid int,contentA varchar(10))
insert into dataA
select 1 ,'XXX' union all
select 2 ,'XXX'
create table dataB(userid int, contentB varchar(10))
insert into dataB
select 1 ,'XXX' union all
select 1 ,'XXX' union all
select 2 ,'XXX'
go
select --id,
name,
(select COUNT(*) from dataA where userid = t.id) as dataAnum,
(select COUNT(*) from dataB where userid = t.id) as dataBnum
from [user] t
/*
name dataAnum dataBnum
张三 1 2
李四 1 1
*/