name borrow_time return_time A 2001-01-01 12:20:30 2001-01-01 14:25:12 A 2001-01-01 16:20:30 2001-01-02 14:25:12楼主的意思是说上面的一共算2天?
name borrow_time return_time A 2001-01-01 12:20:30 2001-01-01 14:25:12 A 2001-01-01 16:20:30 2001-02-01 14:25:12应该为 name borrow_time return_time A 2001-01-01 12:20:30 2001-01-01 14:25:12 A 2001-01-01 16:20:30 2001-01-02 14:25:12 这两次为2天,而不是三天!!
select name,sum(trunc(to_date(return_time,'yyyy-mm-dd'))-trunc(to_date(borrow_time,'yyyy-mm-dd'))+1) as 借出总天数 from tbname group by name
create table t (name varchar2(20), b1 varchar2(20), r1 varchar2(20) )
insert into t values('a','2001-01-01','2001-01-01'); insert into t values('a','2001-01-01','2001-01-02'); insert into t values('a','2001-02-02','2001-02-06'); insert into t values('b','2001-01-01','2001-01-04'); insert into t values('b','2001-05-01','2001-05-02'); commit; select name,sum(tt) from (select name,(max(to_date(r1,'yyyy-mm-dd'))-to_date(b1,'yyyy-mm-dd')+1) as tt from t group by name,b1) group by name;NAME SUM(TT) -------------------- ---------- a 7 b 6
select name,sum(decode(to_date(return_time,'yyyy-mm-dd')-to_date(borrow_time,'yyyy-mm-dd')),0,1,to_date(return_time,'yyyy-mm-dd')-to_date(borrow_time,'yyyy-mm-dd')) from t group by name
还有, bzszp(SongZip),按你给的SQL,算出来的A会多出来一天的,A的第一个记录是1天,第二个记录是和第一个记录的借出时间是一样的,你算出来的第二个记录会包含了第一个记录,所以多出来了一天的!
A 2001-01-01 12:20:30 2001-01-01 14:25:12
A 2001-01-01 16:20:30 2001-01-02 14:25:12楼主的意思是说上面的一共算2天?
A 2001-01-01 12:20:30 2001-01-01 14:25:12
A 2001-01-01 16:20:30 2001-02-01 14:25:12应该为
name borrow_time return_time
A 2001-01-01 12:20:30 2001-01-01 14:25:12
A 2001-01-01 16:20:30 2001-01-02 14:25:12
这两次为2天,而不是三天!!
from tbname
group by name
(name varchar2(20),
b1 varchar2(20),
r1 varchar2(20)
)
insert into t values('a','2001-01-01','2001-01-01');
insert into t values('a','2001-01-01','2001-01-02');
insert into t values('a','2001-02-02','2001-02-06');
insert into t values('b','2001-01-01','2001-01-04');
insert into t values('b','2001-05-01','2001-05-02');
commit; select name,sum(tt)
from
(select name,(max(to_date(r1,'yyyy-mm-dd'))-to_date(b1,'yyyy-mm-dd')+1) as tt
from t
group by name,b1)
group by name;NAME SUM(TT)
-------------------- ----------
a 7
b 6
from t
group by name