我写了一句这sql语句:
select stu.name,
min(att.checktime),
max(att.checktime)
from student stu,
attendance att
where
att.stu_obid=stu.id
group by s.idattendance表里面有一个字段是type,记录的是“进校”,“离校”状态,我该怎么写才能让min函数选择的考勤时间是以“进校”这个状态为条件选择,max函数以“离校”状态为条件选择?谢谢~
select stu.name,
min(att.checktime),
max(att.checktime)
from student stu,
attendance att
where
att.stu_obid=stu.id
group by s.idattendance表里面有一个字段是type,记录的是“进校”,“离校”状态,我该怎么写才能让min函数选择的考勤时间是以“进校”这个状态为条件选择,max函数以“离校”状态为条件选择?谢谢~
select stu.name name,
att.checktime checktime,
att.type type
from student stu,
attendance att
where
att.stu_obid=stu.id
group by stu.name,att.checktime,att.type
)
select name,min(checktime),0 from ax where type='进校'
union
select name,0,max(checktime) from ax where type='离校'
attendance表:
id stu_obid checktime type
1 11 2013-9-1 8:12:13 进校
2 11 2013-9-1 12:30:13 离校
3 11 2013-9-1 14:30:13 进校
4 11 2013-9-1 15:30:13 离校我想显示出来的情况是这样:
学生 进校时间 离校时间
11 2013-9-1 8:12:13 2013-9-1 15:30:13
max(decode(att.type,'离校',att.checktime,null))
(
select 1 id,11 stu_obid,'2013-9-1 08:12:13' checktime,'进校' type from dual union all
select 2 id,11 stu_obid,'2013-9-1 12:30:13' checktime,'离校' type from dual union all
select 3 id,11 stu_obid,'2013-9-1 14:30:13' checktime,'进校' type from dual union all
select 4 id,11 stu_obid,'2013-9-1 15:30:31' checktime,'离校' type from dual l
)select stu_obid,
min(decode(type,'进校',checktime)) m_time,
max(decode(type,'离校',checktime)) n_time
from attendance
group by stu_obid stu_obid m_time n_time
---------------------------------------------------------------
1 11 2013-9-1 08:12:13 2013-9-1 15:30:31