--试下是不是这个意思,varchar2是不需要转换就可以取余的,oracle可以自动转换的 where t.DAY_DATE < 25 and trunc(t.WEATHER) = f.PHOTO_CD and ((mod(t.day_date,24)=0 and f.photo_type=1) or f.photo_type=0) order by t.CITY_CD desc, to_number(DAY_DATE)
SQL> set serveroutput on; SQL> declare 2 v_val1 varchar(20) := '48'; 3 v_val2 varchar(20) := '36'; 4 begin 5 dbms_output.put_line('48 mod 24:'||mod(v_val1,24)); 6 dbms_output.put_line('36 mod 24:'||mod(v_val2,24)); 7 end; 8 / 48 mod 24:0 36 mod 24:12 PL/SQL 过程已成功完成。
3楼的可以解决了,多问一句,能不能用when来解决,我之前好像想太多了 一直在用when来做
可以按下面这种用法,但不可以直接取列where t.DAY_DATE < 25 and trunc(t.WEATHER) = f.PHOTO_CD and f.photo_type=(case when mod(t.day_date,24)=0 then 1 else 0 end) order by t.CITY_CD desc, to_number(DAY_DATE)
下面这种是错误的写法,就是我上面说的不可以直接取列,可以试下就知道了where t.DAY_DATE < 25 and trunc(t.WEATHER) = f.PHOTO_CD and (case when mod(t.day_date,24)=0 then f.photo_type=1 else f.photo_type=0 end) order by t.CITY_CD desc, to_number(DAY_DATE)
select (select p.zhs_desc from UBASE.t_Option p where p.option_cd = 'WD1000' and p.option_key = trunc(t.wind_direction)), (select p.zhs_desc from UBASE.t_Option p where p.option_cd = 'WV1001' and p.option_key = trunc(t.wind_velocity)), f.photo_id, f.photo_nm, t.CITY_CD, t.CITY, t.DAY_DATE, t.WEATHER, t.MAX_TEMPERATURE, t.MIN_TEMPERATURE from DLQXJ.T_WEATHER_FORECAST t, dlqxj.t_photo_desc f where t.DAY_DATE < 25 and f.photo_type = (case when mod(t.day_date, 24) = 0 then 1 else 0 end) and trunc(t.WEATHER) = f.PHOTO_CD order by t.CITY_CD desc, to_number(DAY_DATE) asc这是我结合了zhangandli (人生无悔)的 整合了下 (人生无悔)谢了
--试下是不是这个意思,varchar2是不需要转换就可以取余的,oracle可以自动转换的
where t.DAY_DATE < 25
and trunc(t.WEATHER) = f.PHOTO_CD
and ((mod(t.day_date,24)=0 and f.photo_type=1) or f.photo_type=0)
order by t.CITY_CD desc, to_number(DAY_DATE)
SQL> set serveroutput on;
SQL> declare
2 v_val1 varchar(20) := '48';
3 v_val2 varchar(20) := '36';
4 begin
5 dbms_output.put_line('48 mod 24:'||mod(v_val1,24));
6 dbms_output.put_line('36 mod 24:'||mod(v_val2,24));
7 end;
8 /
48 mod 24:0
36 mod 24:12 PL/SQL 过程已成功完成。
3楼的可以解决了,多问一句,能不能用when来解决,我之前好像想太多了 一直在用when来做
and trunc(t.WEATHER) = f.PHOTO_CD
and f.photo_type=(case when mod(t.day_date,24)=0 then 1 else 0 end)
order by t.CITY_CD desc, to_number(DAY_DATE)
and trunc(t.WEATHER) = f.PHOTO_CD
and (case when mod(t.day_date,24)=0 then f.photo_type=1 else f.photo_type=0 end)
order by t.CITY_CD desc, to_number(DAY_DATE)
这个返回的数据只有白天的 晚上的没出来 难道这个问题不能用一个SQL解决 得分成2个了 纠结死我了
from UBASE.t_Option p
where p.option_cd = 'WD1000'
and p.option_key = trunc(t.wind_direction)),
(select p.zhs_desc
from UBASE.t_Option p
where p.option_cd = 'WV1001'
and p.option_key = trunc(t.wind_velocity)),
f.photo_id,
f.photo_nm,
t.CITY_CD,
t.CITY,
t.DAY_DATE,
t.WEATHER,
t.MAX_TEMPERATURE,
t.MIN_TEMPERATURE
from DLQXJ.T_WEATHER_FORECAST t, dlqxj.t_photo_desc f
where t.DAY_DATE < 25
and f.photo_type = (case when mod(t.day_date, 24) = 0 then 1 else 0 end)
and trunc(t.WEATHER) = f.PHOTO_CD
order by t.CITY_CD desc, to_number(DAY_DATE) asc这是我结合了zhangandli
(人生无悔)的 整合了下 (人生无悔)谢了