SQL能完成的逻辑都在SQL中-CSDN博客

decode, case的用法


select t.startCity,t.endCity,t.weightPrice,t.lightPrice,to_char(t.startTime,'hh24:mi:ss') as startTime,t.timeLimit||'hr.' timeLimit,to_char(t.endLimit,'hh24:mi:ss') as endLimit,to_char(t.pupTime,'hh24:mi:ss') as pupTime,'faster' as quotationVersion,decode(t.arrival_date_type,0,'today',1,'tomorrow',2,'1 day',3,'two day') arrivalDateType,(case when 1/0.05>=250 then (case when (weightPrice*1)>30 then (weightPrice*1) else 30 end) else (case when (lightPrice*0.05)>30 then (lightPrice*0.05) else 30 end) end) price from tms.quotation_a t where t.startCity='peka' and t.endCity='shaa' 
union 
select t.startCity,t.endCity,t.weightPrice,t.lightPrice,'' startTime,t.timeLimit/24||'Day' as timeLimit,'' endLimit,to_char(t.pupTime,'hh24:mi:ss') as pupTime,'standard' as quotationVersion,'' arrivalDataType,(case when 1/0.05>=250 then (case when (weightPrice*1)>20 then (weightPrice*1) else 20 end) else (case when (lightPrice*0.05)>20 then (lightPrice*0.05) else 20 end) end) price from tms.quotation_b t where t.startCity='peka' and t.endCity='shaa';

新行插入，既可以从旧行中取值又可以传参数。


insert into message(select sid.nextval id, ? as no,b.subject, ? as content, ? as customer,b.order,b.station,sysdate time_stampt,'提问' type from message b where b.id= ?

导入部分数据到新表


declare
v_license_no varchar(50);
v_station_code varchar(50);
v_cnt1 number;
v_cnt number;

	TYPE T_CURTYPE IS REF CURSOR;
	C_CUR           T_CURTYPE;

begin
v_cnt := 0;
      OPEN C_CUR FOR
select ta.license_no,ta.station_code from truck_archives ta;
      loop 
         FETCH C_CUR INTO v_license_no,v_station_code;
         EXIT WHEN C_CUR%NOTFOUND; 
         insert into truck_station(ttsn_id,license_no,station_code,validation_date)values(s_ttsn_id.nextval,v_license_no,v_station_code,sysdate);
v_cnt := v_cnt+1;
      end loop;
       dbms_output.put_line('v_cnt:'|| v_cnt);

      CLOSE C_CUR;
      --commit;
null;

end;


select substr(tr.name,0,length(tr.name)-2) name,tr.name,tr.dizhi,tr.canshu,tr.xiangmu,tr.xuhao from (select rank() over(PARTITION BY t.dizhi order by t.xuhao asc) rk,t.name,t.dizhi,t.canshu,t.xiangmu,t.xuhao from t_test t where t.xuhao>='1030' ) tr where tr.rk=1;