新建一个表test不知道符不符合你的要求,: create table test(data_date date,amt number(5,1),zh varchar(20));--任意建几条数据,不知是否符合你的要求 insert into test values(to_date('2009-01-01','yyyy-mm-dd'),2100,'zh_01'); insert into test values(to_date('2009-01-02','yyyy-mm-dd'),2200,'zh_01'); insert into test values(to_date('2009-01-03','yyyy-mm-dd'),2400,'zh_01'); insert into test values(to_date('2009-01-01','yyyy-mm-dd'),2500,'zh_02');insert into test values(to_date('2009-01-03','yyyy-mm-dd'),0,null); insert into test values(to_date('2009-01-01','yyyy-mm-dd'),0,null);insert into test values(to_date('2009-01-01','yyyy-mm-dd'),1200,null); insert into test values(to_date('2009-01-03','yyyy-mm-dd'),1300,null); insert into test values(to_date('2009-01-03','yyyy-mm-dd'),1600,null); --帐号不存在暂时用null替代,没有更多的判断条件。select count(*) total_counts,sum(amt) total_amt from test where (amt<>0 and zh is not null and data_date=to_date('2009-01-01','yyyy-mm-dd')) or (amt=0 and zh is null and data_date=to_date('2009-01-03','yyyy-mm-dd')) and to_date('2009-01-01','yyyy-mm-dd')<to_date('2009-01-03','yyyy-mm-dd'); --total_counts是总条数,total_amt是总金额
八成你是误会了楼主的意思了。条件是and的关系,我觉得
select sum(atm) from tab where data_date=date1 zh in (select zh from tab where data_date=date2 and atm=0) and atm!=0 select count(zh) from tab where data_date=date1 zh in (select zh from tab where data_date=date2 and atm=0) and atm!=0应该就可以了吧
create table test(data_date date,amt number(5,1),zh varchar(20));--任意建几条数据,不知是否符合你的要求
insert into test values(to_date('2009-01-01','yyyy-mm-dd'),2100,'zh_01');
insert into test values(to_date('2009-01-02','yyyy-mm-dd'),2200,'zh_01');
insert into test values(to_date('2009-01-03','yyyy-mm-dd'),2400,'zh_01');
insert into test values(to_date('2009-01-01','yyyy-mm-dd'),2500,'zh_02');insert into test values(to_date('2009-01-03','yyyy-mm-dd'),0,null);
insert into test values(to_date('2009-01-01','yyyy-mm-dd'),0,null);insert into test values(to_date('2009-01-01','yyyy-mm-dd'),1200,null);
insert into test values(to_date('2009-01-03','yyyy-mm-dd'),1300,null);
insert into test values(to_date('2009-01-03','yyyy-mm-dd'),1600,null);
--帐号不存在暂时用null替代,没有更多的判断条件。select count(*) total_counts,sum(amt) total_amt from test where (amt<>0 and zh is not null and data_date=to_date('2009-01-01','yyyy-mm-dd'))
or (amt=0 and zh is null and data_date=to_date('2009-01-03','yyyy-mm-dd')) and to_date('2009-01-01','yyyy-mm-dd')<to_date('2009-01-03','yyyy-mm-dd');
--total_counts是总条数,total_amt是总金额
八成你是误会了楼主的意思了。条件是and的关系,我觉得
zh in (select zh from tab where data_date=date2 and atm=0)
and atm!=0
select count(zh) from tab where data_date=date1
zh in (select zh from tab where data_date=date2 and atm=0)
and atm!=0应该就可以了吧