多table联结查询有关问题

多table联结查询问题
请问，想把这句代码

SQL code

select  count(*) from TM_teacherprofile where jobcategory1='专职'

得出的结果，拼到下面这段代码里面，并与里面的count(distinct A.SubjectsClassID) 相除得出百分比。这个百分比的结果跟下面的代码结果一起显示出来。请问要怎样联结？？
请高手指教。谢谢！

SQL code

select    min(sc.subjectsClassname) as subjectsClassName,        min(s.subjectname) as subjectname,        min(tp.jobcategory1) as jobcategory1,        min(g.gradename) as gradename,        count(distinct A.SubjectsClassID) as ClassCount              from BI_SubjectLessonForSalary as A    left join    BI_subjects s on A.subjectID=s.subjectID    left join     BI_Grades g on s.gradeID=g.gradeID    left join    Bi_subjectsClass sc on A.subjectsClassID=sc.subjectsClassID    left join    Tm_TeacherProfile tp on A.teacherID=tp.ID1            where A.TeacherID is not null and A.teacherID>0 and tp.jobcategory1='专职'group by s.subjectname  

[解决办法]

SQL code

select  min(sc.subjectsClassname) as subjectsClassName,        min(s.subjectname) as subjectname,        min(tp.jobcategory1) as jobcategory1,        min(g.gradename) as gradename,        count(distinct A.SubjectsClassID) as ClassCount,        (select  count(*) from TM_teacherprofile where jobcategory1='专职')*100.0/        nullif(count(distinct A.SubjectsClassID),0) as Ratefrom BI_SubjectLessonForSalary as Aleft join BI_subjects s on A.subjectID=s.subjectIDleft join BI_Grades g on s.gradeID=g.gradeIDleft join Bi_subjectsClass sc on A.subjectsClassID=sc.subjectsClassIDleft join Tm_TeacherProfile tp on A.teacherID=tp.ID1     where A.TeacherID is not null and A.teacherID>0 and tp.jobcategory1='专职'group by s.subjectname
[解决办法]
try
SQL code
select    min(sc.subjectsClassname) as subjectsClassName,        min(s.subjectname) as subjectname,        min(tp.jobcategory1) as jobcategory1,        min(g.gradename) as gradename,        -------------------------------------        1.0*count(distinct A.SubjectsClassID)/(count(tp1.*) as ClassCount          -------------------------------------            from BI_SubjectLessonForSalary as A    left join    BI_subjects s on A.subjectID=s.subjectID    left join     BI_Grades g on s.gradeID=g.gradeID    left join    Bi_subjectsClass sc on A.subjectsClassID=sc.subjectsClassID    left join    Tm_TeacherProfile tp on A.teacherID=tp.ID1    -------------------------------------------------------    left join    Tm_TeacherProfile tp1 on A.teacherID=tp1.ID1 and tp1.jobcategory1='专职'         -------------------------------------------------------    where A.TeacherID is not null and A.teacherID>0 and tp.jobcategory1='专职'    group by s.subjectname
[解决办法]
select    min(sc.subjectsClassname) as subjectsClassName,
       min(s.subjectname) as subjectname,
       min(tp.jobcategory1) as jobcategory1,
       min(g.gradename) as gradename,
       count(distinct A.SubjectsClassID) as ClassCount，
       count(distinct A.SubjectsClassID)/count(tp.teacherID)*1.0,--注意0的判断
     
   from BI_SubjectLessonForSalary as A
   left join
   BI_subjects s on A.subjectID=s.subjectID
   left join  
   BI_Grades g on s.gradeID=g.gradeID
   left join
   Bi_subjectsClass sc on A.subjectsClassID=sc.subjectsClassID
   left join
   Tm_TeacherProfile tp on A.teacherID=tp.ID1    
 
  
   where A.TeacherID is not null and A.teacherID>0 and tp.jobcategory1='专职'
group by s.subjectname
[解决办法]
SQL code
declare @temp int = (select  count(*) from TM_teacherprofile where jobcategory1='专职')select    min(sc.subjectsClassname) as subjectsClassName,        min(s.subjectname) as subjectname,        min(tp.jobcategory1) as jobcategory1,        min(g.gradename) as gradename,        count(distinct A.SubjectsClassID) as ClassCount,        (100 * count(distinct A.SubjectsClassID) / @temp) as Rate            from BI_SubjectLessonForSalary as A    left join    BI_subjects s on A.subjectID=s.subjectID    left join     BI_Grades g on s.gradeID=g.gradeID    left join    Bi_subjectsClass sc on A.subjectsClassID=sc.subjectsClassID    left join    Tm_TeacherProfile tp on A.teacherID=tp.ID1            where A.TeacherID is not null and A.teacherID>0 and tp.jobcategory1='专职'group by s.subjectname 

 

查看更多 下一篇