原表CITY_DAY
id,
city, //城市
pollution_indeces,
main_pollutant,
grade,
status, //优 或者 良 或者 轻度污染
oper_date,//日期
show_state 存储过程
create or replace procedure PROC_TO_T_PARAMETER is l_airBestCity varchar2(50);
l_currentDay date;
l_air30daysI varchar2(100);
l_errorcode varchar2(100);
begin
-- author sunchengliang
--当天时间
select max(tt.oper_date) into l_currentDay from city_day tt; --当天空气质量最好的城市
select t.city
into l_airBestCity
from city_day t
where t.oper_date = l_currentDay
and t.pollution_indeces =
(select min(ttt.pollution_indeces)
from city_day ttt
where ttt.oper_date = l_currentDay); --30内一级天数最多的城市
select tt.city
into l_air30daysI
from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city
order by total1 desc) tt
where tt.total1 = (select max(sum(decode(grade, 'Ⅰ', 1, 0))) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city) and rownum=1;
--更新到db
update T_PARAMETER t
set t.p_content = '今天空气量最好的城市是:' || l_airBestCity || '#' ||
'最近30天内空气质量一级天数最多的城市:'||l_air30daysI
where t.p_type = '1';
Commit;
exception
When Others Then
l_errorcode := sqlerrm;
Rollback;
end PROC_TO_T_PARAMETER;其中两个重要查询个人以为比较罗嗦。有没有好的方法查询.
id,
city, //城市
pollution_indeces,
main_pollutant,
grade,
status, //优 或者 良 或者 轻度污染
oper_date,//日期
show_state 存储过程
create or replace procedure PROC_TO_T_PARAMETER is l_airBestCity varchar2(50);
l_currentDay date;
l_air30daysI varchar2(100);
l_errorcode varchar2(100);
begin
-- author sunchengliang
--当天时间
select max(tt.oper_date) into l_currentDay from city_day tt; --当天空气质量最好的城市
select t.city
into l_airBestCity
from city_day t
where t.oper_date = l_currentDay
and t.pollution_indeces =
(select min(ttt.pollution_indeces)
from city_day ttt
where ttt.oper_date = l_currentDay); --30内一级天数最多的城市
select tt.city
into l_air30daysI
from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city
order by total1 desc) tt
where tt.total1 = (select max(sum(decode(grade, 'Ⅰ', 1, 0))) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city) and rownum=1;
--更新到db
update T_PARAMETER t
set t.p_content = '今天空气量最好的城市是:' || l_airBestCity || '#' ||
'最近30天内空气质量一级天数最多的城市:'||l_air30daysI
where t.p_type = '1';
Commit;
exception
When Others Then
l_errorcode := sqlerrm;
Rollback;
end PROC_TO_T_PARAMETER;其中两个重要查询个人以为比较罗嗦。有没有好的方法查询.
解决方案 »
- ORACLE9能装在四核CPU的机器上吗?
- 求救数据库回滚脚本怎么写?
- 简单的数据更新,各位帮我看看
- 一个比较弱的问题,oracle的字符集和数据库的字符集有关系么?
- pro*c问题
- 游标OPEN CURSOR时就已经可以知道结果集是否为空了吗?
- 请教 , 我要将ORACLE数据库里的所有数字型的默认值设为0.可以吗???????????
- 我是个小鸟 那位可以告诉我 oracle8i如何导入导出数据 如何才能登陆到oracle management server
- oracle中的job能否调用EXE文件?
- 怎样取出小数点后两位?
- 大家帮忙看看这个存储过程有什么问题,编译不通过
- 在线等~~新创建用户不能登陆的问题
into l_air30daysI
from (select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city
order by total1 desc) tt
where tt.total1 = (select max(sum(decode(grade, 'Ⅰ', 1, 0))) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city) and rownum=1; 可以改成select tt.city
into l_air30daysI
from (
select aa.city,row_number() over (order by total1 desc) rn
(select t.city, sum(decode(grade, 'Ⅰ', 1, 0)) total1
from city_day t
where t.oper_date > l_currentDay - 30
and t.oper_date < l_currentDay
group by t.city
) aa) tt
where tt.rn =1
into l_airBestCity
from city_day t
where t.oper_date = l_currentDay
and t.pollution_indeces =
(select min(ttt.pollution_indeces)
from city_day ttt
where ttt.oper_date = l_currentDay);
改成
select t.city
into l_airBestCity
from (
select city,row_num() over (order by pollution_indeces) rn
from city_day
where oper_date = l_currentDay ) t
where t.rn=1
into l_airBestCity
from (
select city,row_number() over (order by pollution_indeces) rn
from city_day
where oper_date = l_currentDay ) t
where t.rn=1
是不是从内层查询开始写起,然后再到外修改.
select t.city, sum(decode(t.grade,'Ⅰ',1,0)) total1
from city_day t where t.oper_date > to_date('2007-11-20','yyyy-MM-dd')-30
and t.oper_date < to_date('2007-11-20','yyyy-MM-dd') group by t.city order by total1 desc
然后,只要取第一条记录就可以了.想简单了.