请教一个关于sql统计的问题(续前题)

Question

请教一个关于sql统计的问题。已有表chengji为 
id  fenlei      yi        er        san 
---------------------------------------------- 
1    游泳      一班,二班    四班        一班 
2    跳高        二班    一班,二班      三班 
3    跳水        一班      三班        四班 要统计为： 
banji    yi        er      san 
------------------------------------- 
一班：    2        1        1 
二班：    2        1        0 
三班：    0        1        1 
...      ...      ...      ... 要怎样实现啊？
前面的帖子很多人作答了，谢谢，不过少了一个条件，就是有并列情况出现，怎么解决？谢谢大家

Answer 1

create table chengji(id int,fenlei varchar(10), yi varchar(10),er varchar(10),san varchar(10))
insert into chengji select 1,'游泳','一班,二班','四班','一班'
insert into chengji select 2,'跳高','二班','一班,二班','三班'
insert into chengji select 3,'跳水','一班','三班','四班'
GOselect   banci,
yi=sum(case when tmp=1 then 1 else 0 end),
er=sum(case when tmp=2 then 1 else 0 end),
san=sum(case when tmp=3 then 1 else 0 end)
from
(
SELECT
    SUBSTRING(A.yi,B.number,CHARINDEX(',',A.yi+',',B.number)-B.number) AS [banci],
    A.id,
    1 as tmp
FROM  chengji as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.yi)
            AND SUBSTRING(','+A.yi,B.number,1)=','UNION ALLSELECT
    SUBSTRING(A.er,B.number,CHARINDEX(',',A.er+',',B.number)-B.number) AS [banci],
    A.id,
    2 as tmp
FROM  chengji as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.er)
            AND SUBSTRING(','+A.er,B.number,1)=','UNION ALLSELECT
    SUBSTRING(A.san,B.number,CHARINDEX(',',A.san+',',B.number)-B.number) AS [banci],
    A.id,
    3 as tmp
FROM  chengji as  A
    JOIN master.dbo.spt_values AS B
        ON B.type='p' AND B.number BETWEEN 1 AND LEN(A.san)
            AND SUBSTRING(','+A.san,B.number,1)=','
) XX
group by bancidrop table chengji/*
banci      yi          er          san         
---------- ----------- ----------- ----------- 
一班         2           1           1
二班         2           1           0
三班         0           1           1
四班         0           1           1
*/

Answer 2

--如果存在班级表
select
    b.banji,
    yi= sum(case when charindex(','+b.banji+',',','+a.yi +',')>0 then 1 else 0 end),
    er= sum(case when charindex(','+b.banji+',',','+a.er +',')>0 then 1 else 0 end),
    san=sum(case when charindex(','+b.banji+',',','+a.san+',')>0 then 1 else 0 end)
from
    成绩表 a,班级表 b
group by
    b.banji