oracle在查询结果加一条记录,Oracle学习之一: 把 SELECT 查询结果插入一个表中

今天写的一个语句如下:

INSERT INTO OAS.DAILY_MIGUSER

(IMPDATE,bigareaid,PROVINCEID,CITYID,NEWUSER,TOTALUSER,NEWACTIVEUSER,TOTALACTIVEUSER,OFFUSER,TOTALOFFUSER)

select trunc(sysdate),0 bigareaid,b.provinceid, a.cityid,

sum(case when to_date('2008-08-22', 'yyyy-mm-dd')=effectivedate then 1 else 0 end) newuser,

count(*) totaluser,

sum(case when to_date('2008-08-22', 'yyyy-mm-dd')=effectivedate then 1 else 0 end) newactiveuser,

sum(case when activedate = null then 1 else 0 end) totalactiveuser,

sum(case when to_date('2008-08-02', 'yyyy-mm-dd')=trunc(expirationdate)  then 1 else 0 end) newoffuser,

sum(case when expirationdate=null  then 0 else 1 end) totaloffuser

from miguser_baseinfo a, (select distinct cityid,provinceid from city) b

where a.cityid=b.cityid

group by b.provinceid,a.cityid

在一个查询语句中记录各种不同情况的出现次数可以用 case when expresion... then ... else ... end. 这种语句肯写是比挨个 select count(*) from ... where expresion... 的方式要方便得多.