当前位置: 代码迷 >> Oracle面试 >> 求一sql语句,30分解决思路
  详细解决方案

求一sql语句,30分解决思路

热度:2804   发布时间:2013-02-26 00:00:00.0
求一sql语句,30分
该sql语句分二部分,第一部分也就是空格以上的语句,实现将第二部分的查询结果插入
gps_warnstat,但是第二部分的查询结果如下面所示,只有二个字段的值,其中autoid
是递增,可以写成gps_warnstatic.nextval,datetime是插入时间,在oracle中为sysdate,可以整个语句执行报错:

insert   into   gps_warnstat(autoid,user_number,datetime,sum)
  --values(gps_warnstatic.nextval,user_number,SYSDATE,stat)

select   user_number,  
sum(case   user_number   when   user_number   then   1   when   user_number   then   1   end   )   stat
from   gps_alarmrec   where   flag= '0 '
group   by   user_number

第二部分的查询结果
user_number               stat
13308479054 2
13308479104 1
8613308457639 1
8613308462614 2
8613319592850 22

------解决方案--------------------------------------------------------
insert into gps_warnstat(autoid,user_number,datetime,sum)
--values(gps_warnstatic.nextval,user_number,SYSDATE,stat)

select gps_warnstatic.nextval, user_number, sysdate,
sum(case user_number when user_number then 1 when user_number then 1 end ) stat
from gps_alarmrec where flag= '0 '
group by user_number
  相关解决方案