select [First Name],[Last Name],Class,
(
select count([Date]) from Attendence where [Roll Number] ='1' and [Student Status] ='P'
) as [No Of Present],
(
select count([Date]) from Attendence where [Roll Number] ='1' and [Student Status] ='A'
)as [No Of Absent]
from Attendence
where [Date] between '2018-09-1' and '2018-09-30'
这是我为我的软件编写的,但它没有给出正确的结果。我想计算学生的在场和缺席数量。
使用条件聚合:
select [First Name],[Last Name],Class from
count(case [Student Status] ='P' then 1 end) as [No Of Present],
count(case [Student Status] ='A' then 1 end)as [No Of Absent]
from Attendence where [Date] between '2018-09-1' and '2018-09-30' and [Roll Number] ='1'
group by [First Name],[Last Name],Class
你的子查询与外部查询不同,它应该是相关的:
但是,您可以使用case
表达式而不是两个子查询在单个select
语句中执行此操作:
select [First Name], [Last Name], Class,
sum(case when [Student Status] = 'P' then 1 else 0 end) as [No Of Present],
sum(case when [Student Status] = 'A' then 1 else 0 end) as [No Of Absent]
from Attendence at
where [Date] between '2018-09-1' and '2018-09-30' and [Roll Number] = '1'
group by [First Name], [Last Name], Class;
我认为您的查询可能有效,但您忘记使用不同。请找到以下更正的查询:
select distinct [First Name],[Last Name],Class,
(
select count([Date]) from Attendence where [Roll Number] ='1' and [Student Status] ='P'
) as [No Of Present],
(
select count([Date]) from Attendence where [Roll Number] ='1' and [Student Status] ='A'
)as [No Of Absent]
from Attendence
where [Date] between '2018-09-1' and '2018-09-30'
group by [First Name],[Last Name],Class