有两张表,表结构如下:
学员表:SU_ID(学员编码),SU_Name(姓名),SU_Pid(身份证),SU_Sex(性别),SU_Work(工作)等
成绩表:SC_LeaContent(学习内容),SC_StarDate(开始时间),SC_EndDate(结束时间),SC_Results(学时成绩),SU_ID(学员编码)现在我要查询在当前表内时间范围在2008-1-1到2008-12-31,成绩累计:100个学时的记录请问一下大家这样我应该如何查询
学员表:SU_ID(学员编码),SU_Name(姓名),SU_Pid(身份证),SU_Sex(性别),SU_Work(工作)等
成绩表:SC_LeaContent(学习内容),SC_StarDate(开始时间),SC_EndDate(结束时间),SC_Results(学时成绩),SU_ID(学员编码)现在我要查询在当前表内时间范围在2008-1-1到2008-12-31,成绩累计:100个学时的记录请问一下大家这样我应该如何查询
where exists(
select 1 from 成绩表 where su_id=t.su_id and sc_stardate between '2008-1-1' and '2008-12-31'
group by su_id having sum(SC_EndDate-SC_StarDate)>=100
)
--另外,是统计成绩累计大于等于100的记录吧?
select su_id from 成绩表 group by su_id having sum(sc_results)>=100
from 学员表 a , 成绩表 b
where a.SU_ID = b.SU_ID and year(b.SC_StarDate) = 2008 and year(b.SC_EndDate) = 2008
group by a.SU_ID , a.SU_Name
having (sum(datediff(hh,b.SC_StarDate,b.SC_EndDate))) >= 100
--另外,是统计成绩累计大于等于100的记录吧?
select su_id from (
select * from 成绩表 where sc_stardate>='2008-1-1' and sc_enddate<='2008-12-31'
)T group by su_id having sum(sc_results)>=100
不是的,每门成绩都是在一个时间段内发生
SC_EndDate-SC_StarDate
然后统计成绩总量!
可能我没有描述清楚我的问题!
见谅!
select * from [学员表]
where SU_ID In
(
select SU_ID from [学员表] a join [学员表] b on a.SU_ID = b.SU_ID where SC_StarDate >= '2008-1-1' and SC_EndDate <= '2008-12-31'
group by SU_ID having sum(SC_Results)=100
)
select su_id from (
select * from 成绩表 where sc_stardate>='2008-1-1' and sc_enddate<='2008-12-31'
)T group by su_id having sum(sc_results)>=100
)