SQL Server中与内部查询相关的问题

Question

我在SQL Server数据库中有以下表：

(1) StudentMaster (StudentId, StudentName)

(2) SubjectMaster (SubjectId, SubjectName)

(3) AttendanceMaster (AttendanceId,StudentId,SubjectId,Attendance,Date)


Data in AttendanceMaster can be in Following format :

AttendanceMaster :
           AttendanceId   StudentId   SubjectId    Attendance   Date
              3001          33          1            P           1/1/2011 
              3001          57          2            P1          1/2/2011 
              3001          33          1            P           1/3/2011 
              3001          57          2            P2          1/4/2011 
              3001          33          1            P1          1/5/2011  

我想以以下格式获取SubjectWise个人出勤详细信息：

StudentName  SubjectName   Total(P)  Total(P1)  Total(P2)
 Ghanshyam     Maths         90        10          5
 John          Maths         85        15          5
 Ghanshyam     Science       70        20          15
 John          Science       80        30          5  

我尝试了以下查询：

select StudentName, SubjectName,
(select count(*) from AttendanceMaster innerAM where innerAM.StudentId = StdM.StudentId and innerAM.SubjectId=SubM.SubjectId and innerAM.Attendance = 'P') as Total(P),
(select count(*) from AttendanceMaster innerAM where innerAM.StudentId = StdM.StudentId and innerAM.SubjectId=SubM.SubjectId and innerAM.Attendance = 'P1') as Total(P1),
(select count(*) from AttendanceMaster innerAM where innerAM.StudentId = StdM.StudentId and innerAM.SubjectId=SubM.SubjectId and innerAM.Attendance = 'P1') as Total(P2)
from AttendanceMaster AM inner join StudentMaster StdM on AM.StudentId = StdM.StudentId
inner join SubjectMaster SubM on AM.SubjectId = SubM.SubjectId

我得到了结果，但执行时间太长。。(大约5到6分钟)

so what can i do to decrease execution time...

另外，编写一个查询来获得总计(P)、总计(P1)、总计(P2)也是正确的方式吗？请指定其他SQL语法

谢谢

Answer 1

尝尝这个。如果您的查询仍然运行缓慢，则需要检查表上是否有索引。如果你的表很大，并且没有索引，它仍然会很慢。

select
    StM.StudentName,
    SbjM.SubjectName,
    count(case when AtM.Attendance = 'P' then 1 else null end) as Total(P),
    count(case when AtM.Attendance = 'P1' then 1 else null end) as Total(P1),
    count(case when AtM.Attendance = 'P2' then 1 else null end) as Total(P2)
from StudentMaster  as StM
    inner join AttendanceMaster as AtM on AtM.StudentId = StM.StudentId
    inner join SubjectMaster as SbjM on SbjM.SubjectId = AtM.SubjectId
group by
    StM.StudentName,
    SbjM.SubjectName

如果列Attendance除了P、P1和P2之外没有值，要计算总数，只需添加count(*) as Total即可。如果除了P、P1和P2之外还有其他值，则有两种方法。首先-您可以在where子句的('P'，'P1'，'P2')中添加AtM.Attendance：

select
    StM.StudentName,
    SbjM.SubjectName,
    count(case when AtM.Attendance = 'P' then 1 else null end) as Total(P),
    count(case when AtM.Attendance = 'P1' then 1 else null end) as Total(P1),
    count(case when AtM.Attendance = 'P2' then 1 else null end) as Total(P2),
    count(*) as Total
from StudentMaster  as StM
    inner join AttendanceMaster as AtM on AtM.StudentId = StM.StudentId
    inner join SubjectMaster as SbjM on SbjM.SubjectId = AtM.SubjectId
where AtM.Attendance in ('P', 'P1', 'P2')
group by
    StM.StudentName,
    SbjM.SubjectName

或者你可以这样写

select
    StM.StudentName,
    SbjM.SubjectName,
    count(case when AtM.Attendance = 'P' then 1 else null end) as Total(P),
    count(case when AtM.Attendance = 'P1' then 1 else null end) as Total(P1),
    count(case when AtM.Attendance = 'P2' then 1 else null end) as Total(P2),
    count(case when AtM.Attendance in ('P', 'P1', 'P2') then 1 else null) as Total
from StudentMaster  as StM
    inner join AttendanceMaster as AtM on AtM.StudentId = StM.StudentId
    inner join SubjectMaster as SbjM on SbjM.SubjectId = AtM.SubjectId
group by
    StM.StudentName,
    SbjM.SubjectName

Answer 2

select MAX(sm.StudentName) as StudentName,
       MAX(subm.SubjectName) as SubjectName,
       SUM(CASE WHEN am.Attendance='P' then 1 else 0 end) as TotalP,
       SUM(CASE WHEN am.Attendance='P1' then 1 else 0 end) as TotalP1,
       SUM(CASE WHEN am.Attendance='P2' then 1 else 0 end) as TotalP2

from AttendanceMaster am inner join StudentMaster sm
on am.StudentId=sm.StudentId
inner join SubjectMaster subm on am.SubjectId=subm.SubjectId

group by am.StudentId,
         am.SubjectId