SQL集锦

    ---批量删除重复数据 SQL 
--    delete from  TT_VS_ORDER_HISTORY where HISTORY_ID in (
--    select HISTORY_ID from (
--    select count(1) num ,min(HISTORY_ID) HISTORY_ID, ORDER_ID, CHANGE_STATUS from TT_VS_ORDER_HISTORY 
--    where CHANGE_STATUS ='70031006'  group by ORDER_ID,CHANGE_STATUS 
--    ) where num >1
--    );

    --批量插入
    --insert into BB select distinct remark from TT_VS_ORDER ;
    
    --create table BB like AA ;
    
    --select distinct remark into CGCSLDMS.BB from TT_VS_ORDER;
    
    --数据库表备份
    --create table TT_VS_SALES_REPORT_20160727 like TT_VS_SALES_REPORT ;
    --insert into TT_VS_SALES_REPORT_20160727 select * from TT_VS_SALES_REPORT;
    
    --数据库表从备份表中还原
    --delete from TT_VS_SALES_REPORT;
    --insert into TT_VS_SALES_REPORT select * from TT_VS_SALES_REPORT_20160727;
    
    
    
    --清空表数据
    delete from AA;
    
     --批量更新
     --第一种  256
     update TT_VS_ORDER t set t.DEAL_ORDER_AFFIRM_DATE = (
     select  tvoh.CREATE_DATE from TT_VS_ORDER_HISTORY tvoh where t.ORDER_ID = tvoh.ORDER_ID and tvoh.CHANGE_STATUS ='70031006' 
     )
     where exists (select 1 from TT_VS_ORDER_HISTORY t1 where t1.CHANGE_STATUS = '70031006' and t1.ORDER_ID = t.ORDER_ID );
     
     --第二种merge into  146
     merge into TT_VS_ORDER t
      using (select b.ORDER_ID , b.CREATE_DATE from TT_VS_ORDER_HISTORY b where b.CHANGE_STATUS ='70031006') a
         on (t.order_id = a.order_id)
       when matched then
         update set t.DEAL_ORDER_AFFIRM_DATE = a.CREATE_DATE; 
         
     --第三种 利用视图做批量更新
     --error 42807
--     update ( select tvo.DEAL_ORDER_AFFIRM_DATE, tvoh.CREATE_DATE from TT_VS_ORDER tvo
--     inner join TT_VS_ORDER_HISTORY tvoh on tvo.ORDER_ID = tvoh.ORDER_ID 
--     where tvoh.CHANGE_STATUS = '70031006' )
--     set DEAL_ORDER_AFFIRM_DATE = CREATE_DATE ;
     
     
     --无条件同时插入
     --DB2不支持
--     INSERT ALL
--      INTO AA( REMARK) VALUES(REMARK1)
--      INTO BB( REMARK) VALUES(REMARK1)
--     SELECT DISTINCT REMARK1 FROM TT_VS_ORDER ;

    --数值校验    
    --正整数校验  - 不为正整数
    SELECT * FROM TM_DEALER T2 WHERE LENGTH(TRIM(TRANSLATE(T2.DEALER_CODE,'','0123456789'))) > 0;
    --正整数校验  - 为正整数
    SELECT * FROM TM_DEALER T2 WHERE LENGTH(TRIM(TRANSLATE(T2.DEALER_CODE,'','0123456789'))) = 0;
    
    --select TRIM(TRANSLATE(dealer_code,'','0123456789')) from tm_dealer
    
    
    --小数校验  - 不为小数或不是正确的小数
    SELECT * FROM AA WHERE LENGTH(TRIM(TRANSLATE(REMARK,'','.0123456789'))) > 0 
          OR (LENGTH(TRIM(TRANSLATE(REMARK,'','.0123456789'))) = 0  AND LENGTH(TRIM(TRANSLATE(TRANSLATE(REMARK,'A','.'),'','0123456789'))) > 1);
          
    --小数校验  - 为小数或正整数
    SELECT * FROM AA WHERE LENGTH(TRIM(TRANSLATE(REMARK,'','.0123456789'))) = 0 
          AND LENGTH(TRIM(TRANSLATE(TRANSLATE(REMARK,'A','.'),'','0123456789'))) < 2;
          
    insert into AA values('2016/07/27');  
    delete from AA
    
    --非空数据校验
    select * from aa where REMARK is null or REMARK = '';
    
    --select * from aa where nvl(trim(REMARK),'0') = '0';
    
    select * from aa where length(nvl(trim(REMARK),'')) = 0;
    
    --判断数据是否存在  存在用 exists 不存在用 not exists
    select * from AA t1 where not exists (
      select 1 from TM_DEALER t2 where t1.TEXT = t2.DEALER_CODE
    )
    
    --重复数据校验 先查出重复数据，然后查出所有重复数据并讲行号拼接成一列（行转列）
    select LISTAGG(t1.TEXT , ',') WITHIN GROUP(ORDER BY t1.TEXT) 行号 from AA t1 where exists (
      select 1 from (select count(1),TEXT from AA group by TEXT having count(1) >1 ) t2 where t1.TEXT = t2.TEXT
    )
    group by t1.TEXT;
    
    --时间格式验证
    select iSdate(TEXT),TEXT  from AA;
    
    --替换所有字符
    select rpad('#',length('CL10AR'),'#') from SYSIBM.DUAL ;
    
    --替换字符
    select replace('##10##','#','') from SYSIBM.DUAL ;   
    
    select trim(translate('##10##','','#')) from SYSIBM.DUAL ; 
    
    
    SELECT TO_DATE(TEXT,'YYYY-MM-DD HH24') FROM AA;
    
    insert into AA values('2016-7-27');
    delete from AA;
    
    
    select length(TRANSLATE(TRANSLATE(REMARK,'A','.'),'','0123456789')) from AA;
    
    select MSRP_PRICE, ORDER_NO from tt_vs_order where TT_VS_ORDER.MSRP_PRICE = '';
    
    

     
     
     
--时间验证函数
CREATE OR REPLACE FUNCTION CGCSLDMS.ISDATE ( P_DATE VARCHAR(19) )
  RETURNS INTEGER
BEGIN
  -- 错误标志
  DECLARE v_result INT;
  DECLARE v_date  DATE;
  -- 定义错误处理
  -- 如果发生错误，继续处理
  -- 但是把标志位设置为 0
  DECLARE CONTINUE HANDLER
  FOR
  SQLEXCEPTION SET v_result=0;
  -- 错误标志，首先设置为正常.
  SET v_result = 1;
  IF LENGTH(p_date)=8 THEN 
    SET v_date = TO_DATE(p_date, 'YYYY-MM-DD');
  ELSEIF LENGTH(p_date)=9 THEN 
    SET v_date = TO_DATE(p_date, 'YYYY-MM-DD');
  ELSEIF LENGTH(p_date)=10 THEN 
    IF LENGTH(TRIM(TRANSLATE(p_date,'','0123456789'))) = 0 THEN
      SET v_date = TO_DATE(p_date, 'YYYY-MM-DD HH24');
    ELSE
      SET v_date = TO_DATE(p_date, 'YYYY-MM-DD');
    END IF;
  ELSEIF LENGTH(p_date)=12 THEN
    SET v_date = TO_DATE(p_date, 'YYYY-MM-DD HH24:MI');
  ELSEIF LENGTH(p_date)=13 THEN
    SET v_date = TO_DATE(p_date, 'YYYY-MM-DD HH24');
  ELSEIF LENGTH(p_date)=16 THEN
    SET v_date = TO_DATE(p_date, 'YYYY-MM-DD HH24:MI');
  ELSE
    SET v_date = TO_DATE(p_date, 'YYYY-MM-DD HH24:MI:SS');
  END IF;
  return v_result;
END;