SELECT *, (SELECT COUNT(*)
FROM np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='语文' and c.XqingDate ='2024-11-19' ) as gcywnum ,
(SELECT COUNT(*) FROM np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='数学' and c.XqingDate ='2024-11-19' ) as gcsxnum ,
(SELECT COUNT(*) FROM np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='英语' and c.XqingDate ='2024-11-19' ) as gcyynum ,
(SELECT COUNT(*) FROM np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='其它' and c.XqingDate ='2024-11-19' ) as gcqtnum ,
stuff((select '-'+wcstate from np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='语文' and c.XqingDate ='2024-11-19' for xml path ('') ),1,1,'' ) as ywname ,
stuff((select '-'+wcstate from np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='数学' and c.XqingDate ='2024-11-19' for xml path ('') ),1,1,'' ) as sxname ,
stuff((select '-'+wcstate from np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='英语' and c.XqingDate ='2024-11-19' for xml path ('') ),1,1,'' ) as yyname ,
stuff((select '-'+wcstate from np_Xqing c WHERE c.StudentId = s.StudentId and c.Kmu='其它' and c.XqingDate ='2024-11-19' for xml path ('') ),1,1,'' ) as qtname
FROM np_Student s where s.wttypeid !='午托' order by StudentId asc