;with cte as (
select tc.f_className1 as 班级,ccsh.f_stunums as 考生数, ccsh.f_subject05Avg as 物理,ccsh.f_subject02Avg as 数学,ccsh.f_subject04Avg as 生物,ccsh.f_subject03Avg as 英语,ccsh.f_subject01Avg as 语文,ccsh.f_allavg as 均分,ccsh.F_AllGradeRank as 均分排名,ccsh.f_threeavg as 三科均分,ccsh.F_ThreeGradeRank as 三科均分排名,tt.f_teaname as 班主任
from C_ClassScoreHorizontal as ccsh
left join T_Class as tc on ccsh.f_classguid=tc.f_guid
left join T_Classteacher as tct on tct.f_classguid=ccsh.f_classguid
left join t_teacher as tt on tt.f_guid=tct.f_teaguid
where ccsh.f_examguid='96c17461e806448c8eb0187349d12fe2'
and ccsh.f_ClassGuid in ('f31fab1bf7344488b02176bb8e1c0aaf','205a000fdadd41a29973b951696c9d79','765bef0bc9e54e4aa183d08368de9621','cb5ae722be1d4bf1bf9b30b77dbbcf46','3412df15f4204660a682fe5ecc70b868','67159f5396994705914d6e63f51e08a1','0e6f2a6775e3470ea438782dac2be79b','15aa7c72bcb74fa8bdb8923733bb7dcd','aaf84167f437483a9eed3ba3062883fb','931b2f26bc554f95a805afb31d79b10a','67e3c1aa5457456e85cda815d016f1f4','56e493b3311f4bf18d08ef6a00d0c697','5cec99abe1494de2b8dd4d2bd547401e','d17d2c0fa96f4236b25987831d21d2e1','9de67e734e0c40e0ab7ad1e718934689'))
select 班级,考生数,物理,数学,生物,英语,语文,均分,均分排名,三科均分,三科均分排名,
stuff((select '、'+班主任 from cte where 班级 = t.班级 and 考生数 = t.考生数 for xml path('')) ,1,1,'') as 班主任 from cte t
group by 班级,考生数,物理,数学,生物,英语,语文,均分,均分排名,三科均分,三科均分排名虽然有很多班级，但是查询出来的只有 C_ClassScoreHorizontal 表中存在数据的一个班级，
其他班级都不显示，怎么能让没有数据的班级也显示出来？谢谢了喵~
select tc.f_className1 as 班级,ccsh.f_stunums as 考生数, ccsh.f_subject05Avg as 物理,ccsh.f_subject02Avg as 数学,ccsh.f_subject04Avg as 生物,ccsh.f_subject03Avg as 英语,ccsh.f_subject01Avg as 语文,ccsh.f_allavg as 均分,ccsh.F_AllGradeRank as 均分排名,ccsh.f_threeavg as 三科均分,ccsh.F_ThreeGradeRank as 三科均分排名,tt.f_teaname as 班主任
from C_ClassScoreHorizontal as ccsh
left join T_Class as tc on ccsh.f_classguid=tc.f_guid
left join T_Classteacher as tct on tct.f_classguid=ccsh.f_classguid
left join t_teacher as tt on tt.f_guid=tct.f_teaguid
where ccsh.f_examguid='96c17461e806448c8eb0187349d12fe2'
and ccsh.f_ClassGuid in ('f31fab1bf7344488b02176bb8e1c0aaf','205a000fdadd41a29973b951696c9d79','765bef0bc9e54e4aa183d08368de9621','cb5ae722be1d4bf1bf9b30b77dbbcf46','3412df15f4204660a682fe5ecc70b868','67159f5396994705914d6e63f51e08a1','0e6f2a6775e3470ea438782dac2be79b','15aa7c72bcb74fa8bdb8923733bb7dcd','aaf84167f437483a9eed3ba3062883fb','931b2f26bc554f95a805afb31d79b10a','67e3c1aa5457456e85cda815d016f1f4','56e493b3311f4bf18d08ef6a00d0c697','5cec99abe1494de2b8dd4d2bd547401e','d17d2c0fa96f4236b25987831d21d2e1','9de67e734e0c40e0ab7ad1e718934689'))
select 班级,考生数,物理,数学,生物,英语,语文,均分,均分排名,三科均分,三科均分排名,
stuff((select '、'+班主任 from cte where 班级 = t.班级 and 考生数 = t.考生数 for xml path('')) ,1,1,'') as 班主任 from cte t
group by 班级,考生数,物理,数学,生物,英语,语文,均分,均分排名,三科均分,三科均分排名虽然有很多班级，但是查询出来的只有 C_ClassScoreHorizontal 表中存在数据的一个班级，
其他班级都不显示，怎么能让没有数据的班级也显示出来？谢谢了喵~
select tc.f_className1 as 班级,ccsh.f_stunums as 考生数, ccsh.f_subject05Avg as 物理,ccsh.f_subject02Avg as 数学,ccsh.f_subject04Avg as 生物,ccsh.f_subject03Avg as 英语,ccsh.f_subject01Avg as 语文,ccsh.f_allavg as 均分,ccsh.F_AllGradeRank as 均分排名,ccsh.f_threeavg as 三科均分,ccsh.F_ThreeGradeRank as 三科均分排名,tt.f_teaname as 班主任
from T_Class as tc--班级表跟C_ClassScoreHorizontal表换个位置
left join C_ClassScoreHorizontal as ccsh on ccsh.f_classguid=tc.f_guid
left join T_Classteacher as tct on tct.f_classguid=ccsh.f_classguid
left join t_teacher as tt on tt.f_guid=tct.f_teaguid
where ccsh.f_examguid='96c17461e806448c8eb0187349d12fe2'
and ccsh.f_ClassGuid in ('f31fab1bf7344488b02176bb8e1c0aaf','205a000fdadd41a29973b951696c9d79','765bef0bc9e54e4aa183d08368de9621','cb5ae722be1d4bf1bf9b30b77dbbcf46','3412df15f4204660a682fe5ecc70b868','67159f5396994705914d6e63f51e08a1','0e6f2a6775e3470ea438782dac2be79b','15aa7c72bcb74fa8bdb8923733bb7dcd','aaf84167f437483a9eed3ba3062883fb','931b2f26bc554f95a805afb31d79b10a','67e3c1aa5457456e85cda815d016f1f4','56e493b3311f4bf18d08ef6a00d0c697','5cec99abe1494de2b8dd4d2bd547401e','d17d2c0fa96f4236b25987831d21d2e1','9de67e734e0c40e0ab7ad1e718934689'))
select 班级,考生数,物理,数学,生物,英语,语文,均分,均分排名,三科均分,三科均分排名,
stuff((select '、'+班主任 from cte where 班级 = t.班级 and 考生数 = t.考生数 for xml path('')) ,1,1,'') as 班主任 from cte t
group by 班级,考生数,物理,数学,生物,英语,语文,均分,均分排名,三科均分,三科均分排名
这个条件去掉？
left join T_Class as tc on ccsh.f_classguid=tc.f_guid
--重点应该是这个地方，左链接。左表是C_ClassScoreHorizontal
去掉 ccsh.f_examguid='96c17461e806448c8eb0187349d12fe2' 看看