SQL的学习.

if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (id int,text varchar(4))
insert into #A
select 1,'力量' union all
select 2,'体力' union all
select 3,'邪恶'
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (pid int,whole varchar(2),Aid int)
insert into #B
select 1,'甲',2 union all
select 2,'乙',2 union all
select 3,'丙',3
go

select  pid,whole,[text] from #a a,#b b
where b.aid=a.id

/*
pid         whole text
----------- ----- ----
1           甲     体力
2           乙     体力
3           丙     邪恶
*/

select a.id,case when aid is not null then '有' else '无' end as b表是否被选中,
isnull(num,0) as b表选的数量
 from #a a left join (select Aid,count(1) as num from #b group by aid) as b
on a.id=b.aid
/*
id          b表是否被选中 b表选的数量
----------- ------- -----------
1           无       0
2           有       2
3           有       1
*/