34,576
社区成员
发帖
与我相关
我的任务
分享
select m.alumb_name , isnull(count(1),0)
from alumb m left join photo n
on m.id = n.alumb_id
group by m.alumb_name
if object_id('test.dbo.alumb') is not null drop table alumb
-- 创建数据表
create table alumb
(
id int,
alumb_name varchar(6)
)
go
--插入测试数据
insert into alumb select 1,'同事'
union all select 2,'好友'
union all select 3,'同学'
UNION ALL SELECT 4,'kkk'
go
if object_id('test.dbo.photo') is not null drop table photo
-- 创建数据表
create table photo
(
id int identity(1,1),
alumb_id int,
photo_name varchar(10)
)
go
--插入测试数据
insert into photo select 1,'1000.jpg'
union all select 1,'1001.jpg'
union all select 1,'1002.jpg'
union all select 2,'1.jpg'
union all select 2,'10.jpg'
union all select 3,'100.jpg'
union all select 3,'10021.jpg'
go
--代码实现
select alumb_name,num=count(photo_name)
from alumb a left join photo b
on a.id=b.alumb_id
group by alumb_name
/*
select alumb_name,num=count(photo_name)
from alumb a inner join photo b
on a.id=b.alumb_id
group by alumb_name
*/