请问,想把这句代码select count(*) from TM_teacherprofile where jobcategory1='专职'得出的结果,拼到下面这段代码里面,并与里面的count(distinct A.SubjectsClassID) 相除得出百分比。这个百分比的结果跟下面的代码结果一起显示出来。请问要怎样联结??
请高手指教。谢谢!
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
请高手指教。谢谢!
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
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 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
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
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
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