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... 的方式要方便得多.