oracle中一个关于不同时间复合查询的请教！

Question

select name,sum(anum),sum(bnum) from(
select name,anum,0 from tablea where dtime<to_date('2002/02/11','yyyy'/mm/dd')
union
select name,0,bnum from tablea where dtime>=to_date('2002/02/11','yyyy'/mm/dd'))
group by name;

Answer 1

时间的比较不用细究，只需用Dtime>=LevelTime或Dtim<LevelTime就行

Answer 2

select name,
sum(decode(sign(20020211 - to_number(replace(Dtime,'/',''))),1,Anum,0)) Anum,
sum(decode(sign(20020211 - to_number(replace(Dtime,'/',''))),1,0,bnum)) Bnum
from tableA group by name;NAME             ANUM       BNUM
---------- ---------- ----------
F12                 3         16
K11                14         22已用时间:  00: 00: 00.20
11:09:19 jlanzpa817>

Answer 3

select name,
sum(case to_char(Dtime,'yyyy-mm-dd') < '2002-02-11' then Anum),sum(case to_char(Dtime,'yyyy-mm-dd') >= '2002-02-11' then Bnum) from tableA group by name;这只是个大概意思，没有建表测试。

Answer 4

为什么我的语句里面
select name,sum(A),sum(B) from(
select name,anum as A,0 from tablea where dtime<to_date('2002/02/11','yyyy'/mm/dd')
union
select name,0,bnum as B from tablea where dtime>=to_date('2002/02/11','yyyy'/mm/dd'))
group by name
将提取的字段自定义其名as A,B的时候，会出现无效列名？

Answer 5

select name,
sum(decode(sign(20020211 - to_number(replace(Dtime,'/',''))),1,Anum,0)) Anum,
sum(decode(sign(20020211 - to_number(replace(Dtime,'/',''))),1,0,bnum)) Bnum
from tableA group by name;