其实比较简单,就是对数据进行分组求和,类似于 select count(id) from t_table group by sj
with a as (select '2016-01-01 08:03:02' sj, 1 zt , '入' act from dual union all select '2016-01-01 08:09:01' sj, 1 zt , '入' act from dual union all select '2016-01-01 12:03:02' sj, 2 zt , '出' act from dual union all select '2016-01-01 12:03:02' sj, 1 zt , '入' act from dual union all select '2016-01-01 13:13:02' sj, 1 zt , '出' act from dual union all select '2016-01-01 13:15:01' sj, 1 zt , '出' act from dual union all select '2016-01-01 13:17:01' sj, 1 zt , '入' act from dual union all select '2016-01-01 13:27:01' sj, 1 zt , '入' act from dual union all select '2016-01-01 13:54:02' sj, 1 zt , '入' act from dual union all select '2016-01-01 14:05:02' sj, 1 zt , '入' act from dual union all select '2016-01-01 15:33:02' sj, 1 zt , '出' act from dual union all select '2016-01-01 18:53:02' sj, 1 zt , '出' act from dual ) select substr(sj,1,13),sum(decode(act,'出',1,0)), sum(decode(act,'入',1,0)) from a where zt = 1 group by substr(sj,1,13) order by 1
微信:lantkin
(select '2016-01-01 08:03:02' sj, 1 zt , '入' act from dual
union all
select '2016-01-01 08:09:01' sj, 1 zt , '入' act from dual
union all
select '2016-01-01 12:03:02' sj, 2 zt , '出' act from dual
union all
select '2016-01-01 12:03:02' sj, 1 zt , '入' act from dual
union all
select '2016-01-01 13:13:02' sj, 1 zt , '出' act from dual
union all
select '2016-01-01 13:15:01' sj, 1 zt , '出' act from dual
union all
select '2016-01-01 13:17:01' sj, 1 zt , '入' act from dual
union all
select '2016-01-01 13:27:01' sj, 1 zt , '入' act from dual
union all
select '2016-01-01 13:54:02' sj, 1 zt , '入' act from dual
union all
select '2016-01-01 14:05:02' sj, 1 zt , '入' act from dual
union all
select '2016-01-01 15:33:02' sj, 1 zt , '出' act from dual
union all
select '2016-01-01 18:53:02' sj, 1 zt , '出' act from dual
)
select substr(sj,1,13),sum(decode(act,'出',1,0)),
sum(decode(act,'入',1,0))
from a
where zt = 1
group by substr(sj,1,13)
order by 1