求一条简单的SQL升级版

Question

刚才发了一个帖子请教过，后来发现问题还要难的，只好又上来向高人和热心人求救了。
表a:usera
字段:ida,namea  
值:
1 jack
2 rose
3 sam
4 bill表b：recordb /////字段改成了status1和status2，
字段：idb,status1,status2 
值:
1 jack,rose,sam, jack,rose,
2 jack,          null
3 null           null问题：希望能有个集合，在表a的基础上，在最后增加三列分别是cnt1，cnt2，cntall
cnt1用来计算表a的名字在表b的status1这列记录出现的记录数
cnt2同理
cntall 用来判断，如果cnt1>0,取cnt1的值，如果没有才取cnt2的值(当然cnt>0),否则就=0
结果c：
ida,namea,cnt1,cnt2,cntall
1   jack  2    1    2
2   rose  1    1    1
3   sam   1    0    1
4   bill  0    0    0国际惯例，我又一次感谢高人，热心人和路人甲乙丙丁戊己庚辛壬癸

Answer 1

最近学SQL比较郁闷，提问过好几个SQL，搞不懂什么时候要把搜索出来的结果AS 个A，
也搞不懂结果集是丢在select呢还是left join

Answer 2

if object_id('[usera]') is not null drop table [usera]
go
create table [usera]([ida] int,[namea] varchar(4))
insert [usera]
select 1,'jack' union all
select 2,'rose' union all
select 3,'sam' union all
select 4,'bill'
if object_id('[recordb]') is not null drop table [recordb]
go
create table [recordb]([idb] int,[status1] varchar(13),[status2] varchar(10))
insert [recordb]
select 1,'jack,rose,sam','jack,rose,' union all
select 2,'jack',null union all
select 3,null,null
 
select *,cntall=case when cnt1>0 then cnt1 else cnt2 end
from
(
select a.*,
cnt1=count(distinct b.status1),
cnt2=count(distinct c.status2)
from usera a
left join recordb b
on charindex(','+a.namea+',' ,','+b.status1+',')>0 
left join recordb c
on charindex(','+a.namea+',' ,','+c.status2+',')>0
group by a.ida,a.namea
) t
order by ida/**
ida         namea cnt1        cnt2        cntall
----------- ----- ----------- ----------- -----------
1           jack  2           1           2
2           rose  1           1           1
3           sam   1           0           1
4           bill  0           0           0
**/

Answer 3

create table #usera(ida int,namea varchar(10))
insert #usera select 1 ,'jack'
insert #usera select 2 ,'rose'
insert #usera select 3 ,'sam'
insert #usera select 4 ,'bill'
create table #recordb(idb int,status1 varchar(50),status2 varchar(50))
insert #recordb select 1 ,'jack,rose,sam', 'jack,rose'
insert #recordb select 2 ,'jack', null
insert #recordb select 3 ,null, nullwith ct1 as(
select idb ,value,status from #recordb
unpivot( value for status in([status1],[status2])) unpvt
)select a.*,isnull(b.cnt1,0) as cnt1,isnull(b.cnt2,0) as cnt2 ,
isnull(cnt1,isnull(cnt2,0)) as cntall
from #usera a left join(
select 
value1
,sum(case when status='status1' then 1 else 0 end) as cnt1
,sum(case when status='status2' then 1 else 0 end) as cnt2
from 
(select idb,status,value=convert(xml,'<root><row>'+replace(value,',','</row><row>')+'</row></root>') from ct1)a
outer apply
(select value1=T.C.value('.','nvarchar(100)') from a.value.nodes('/root/row')T(C))b
group by value1
) b on a.namea=b.value1
        ida namea             cnt1        cnt2      cntall
----------- ---------- ----------- ----------- -----------
          1 jack                 2           1           2
          2 rose                 1           1           1
          3 sam                  1           0           1
          4 bill                 0           0           0(4 行受影响)

Answer 4

大家好，我又来了。本着学习再总结的态度，将个人学习修改后的答案发一下，就当存档。
（只怪我当初举例给的原始数据不够谨慎。）
如果原始数据如下
if object_id('[usera]') is not null drop table [usera]
go
create table [usera]([ida] int,[namea] varchar(4))
insert [usera]
select 1,'jack' union all
select 2,'rose' union all
select 3,'sam' union all
select 4,'bill'
if object_id('[recordb]') is not null drop table [recordb]
go
create table [recordb]([idb] int,[status1] varchar(20),[status2] varchar(20))
insert [recordb]
select 1,'jack,rose,sam,','jack,rose,' union all
select 2,'jack,','jack,rose,' union all
select 3,null,'bill'然后SQL就修改成select * from usera
select * from recordb
select *,cntall =(case when cnt1>0 then cnt1 else cnt2 end)
from
(
select a.*,
cnt1 = count(distinct b.idb),
cnt2 = count(distinct c.idb)
from usera a
left join recordb b
on charindex(a.namea,b.status1)>0
left join recordb c
on charindex(a.namea,c.status2)>0
group by a.ida,a.namea) t
order by ida初学left join，就贴说下自己的心得：
如果是多表连续的left join，
1、首现是a表的1记录，
2、然后根据a1记录，扫描b表，
   21、看是否b表的1记录是否有对应，
        3、然后再继续扫描c表1记录，(如果没有d表要继续left join了，)
           31、则就继续扫描c的2记录
           3n、如此循环扫描完c表cn条记录，
   21x、回溯到b表，将b表1记录复制数量为c表的cn条，然后再扫描b表的2记录，然后就是开始重复2步骤了，一致到循环完b表bn条记录，再跳到最外一层，继续a的2记录希望有人看到，然后指点是否正确。