declare @a varchar(8000)
set @a='1#2#3#'set @a=left(replace(@a,'#',','),len(@a)-1)exec('select b.student_name,c.正常,c.迟到,c.旷课 from 学生名单表 b left join (select student_id,sum(case when student_status=1 then 1 else 0 end) 正常,sum(case when student_status=2 then 1 else 0 end) 迟到,sum(case when student_status=3 then 1 else 0 end) 旷课 from 学生考勤表) con b.student_id=c.student_id where b.class_id in ('+@a+')')
set @a='1#2#3#'set @a=left(replace(@a,'#',','),len(@a)-1)exec('select b.student_name,c.正常,c.迟到,c.旷课 from 学生名单表 b left join (select student_id,sum(case when student_status=1 then 1 else 0 end) 正常,sum(case when student_status=2 then 1 else 0 end) 迟到,sum(case when student_status=3 then 1 else 0 end) 旷课 from 学生考勤表) con b.student_id=c.student_id where b.class_id in ('+@a+')')
as
declare @sql varchar(8000),@class varchar(200)
set @class=' and class_id like ''['+replace(@参数,'分隔符','')+']'''
exec('
select * into #t from
(select class_id ,a.student_id,count(case student_status when 1 then a.student_id end) as '正常',
count(case student_status when 2 then a.student_id end) as '迟到',
count(case student_status when 3 then a.student_id end) as '旷课'
--不太清楚re具体内容,假设是个日期,取最近的一次,搂主可以自己更改
,max(re) as '最近一次上课备注说明'from 学生考勤表 as a,学生名单表 as b
where a.student_id=b.student_id '+@class+'
group by class_id,a.student_id) as a')select class_name,student_name,正常,迟到,旷课,最近一次上课备注说明
from 学校班级表 as a,学生名单表 as b,#t as c
where a.class_id=c.class_id and b.student_id=c.student_id
time->上课时间
set @a='1#2#3#'set @a=left(replace(@a,'#',','),len(@a)-1)exec('select b.student_name,c.正常,c.迟到,c.旷课 from 学生名单表 b left join (select student_id,sum(case when student_status=1 then 1 else 0 end) 正常,sum(case when student_status=2 then 1 else 0 end) 迟到,sum(case when student_status=3 then 1 else 0 end) 旷课 from 学生考勤表) con b.student_id=c.student_id where b.class_id in ('+@a+')')
asset @classid=left(replace(@classid,'#',','),len(@classid)-1)
exec ('select b.student_name,a.* from
(select a.student_id,
sum (case when a.student_status=1 then re else 0 end ) as 上课次数,
sum (case when a.student_status=2 then re else 0 end) as 迟到次数,
sum (case when a.student_status=3 then re else 0 end) as 旷课次数
from 学生考勤表 a group by a.student_id) a
inner join 学生名单表 b on a.student_id=b.student_id
where b.class_in in ('''+@classid+''')')
count(*) as 上课次数,
sum(case c.student_status when 2 then 1 else 0 end) as 迟到,
sum(case c.student_status when 3 then 1 else 0 end) as 旷课,
cast('' as varchar(100)) as 备注
from 班级表 a,学生表 b,考勤表 c
where '#' + @班级变量 + '#' like '%#' + class_id + '#%'
and a.class_id = b.class_id and b.student_id = c.student_id
group by a.class_id,student_id,student_name最后一次备注:怎样判断哪条记录是最后一次啊?凭记录的物理顺序吗?
set @a='1#2#3#'set @a=left(replace(@a,'#',','),len(@a)-1)exec('
select b.student_name,c.正常,c.迟到,c.旷课,c.后一次上课时间,c.最近一次上课备注说明 from 学生名单表 b left join (select student_id,sum(case when student_status=1 then 1 else 0 end) 正常,sum(case when student_status=2 then 1 else 0 end) 迟到,sum(case when student_status=3 then 1 else 0 end) 旷课,max(time) 后一次上课时间
,(select top 1 re from 学生考勤表 where student_id=tem.student_id order by time desc) 最近一次上课备注说明 from 学生考勤表 tem group by student) con b.student_id=c.student_id where b.class_id in ('+@a+')')
select b.student_name,a.* from
(select a.student_id,
sum (case when a.student_status=1 then re else 0 end ) as 上课次数,
sum (case when a.student_status=2 then re else 0 end) as 迟到次数,
sum (case when a.student_status=3 then re else 0 end) as 旷课次数,
(select top 1 re from 学生考勤表 where student_id=a.student_id order by 上课日期 desc) as 最近一次上课备注说明
from 学生考勤表 a group by a.student_id) a
inner join 学生名单表 b on a.student_id=b.student_id