分享java帶來(lái)的快樂(lè)

          我喜歡java新東西

          oracle sql 精典

          1替換 字段中的指定位置字符
          select mi_temp_password,Concat(Concat(Substr(mi_temp_password, 1, 1),'1'),Substr(mi_temp_password, 3))
            from new_test t
           where mi_id = 1033209
          2.三個(gè)月內(nèi)登錄過(guò)的用戶列表
          select lastlogindate > Add_months(sysdate, -3) from dual
          3. select *
            from (select selectrow.*, rownum rownum_
                    from (select t.* from ins_b_userinfo t order by id asc) selectrow)
           where rownum_ < 20
             and rownum_ > 1
          4.刪除最大的重復(fù)id
          delete from pi_d_answer
           where id in
                 (select maxid
                    from (select max(id) as maxid, childprojectid, userid, count(*)
                            from pi_d_answer t
                           group by childprojectid, userid
                          having count(*) > 1|-)
                           order by childprojectid asc))
          5.更新sql
          5.1:update pi_d_childproject t set surveyid=(select s.id from pi_d_survey s where t.id=s.childprojectid)
          5.2:update (select t.id, e.earning eearning, t.earning tearning
                    from pi_d_userinfoext e, tmp t
                   where t.id = e.id) rs
             set rs.eearning = rs.tearning
          6.顯示case select 結(jié)果

          SELECT sex,
                 case when (SUBSTR(BIRTHDAY,0,4)  < '1983' and SUBSTR(BIRTHDAY,0,4)  > '1970')  then '比我大的'
                      when (SUBSTR(BIRTHDAY,0,4)  < '1970' and SUBSTR(BIRTHDAY,0,4)  > '1960')  then '比我大的2'
                      else '比我小的' end as ages,
                      count(*)
            FROM PI_V_SAMPLE_CONDITION
           WHERE BIRTHDAY IS NOT NULL
             AND BIRTHDAY <> 0
             AND SEX IN(1,2)
           GROUP BY sex,
                    case when (SUBSTR(BIRTHDAY,0,4)  < '1983' and SUBSTR(BIRTHDAY,0,4)  > '1970')   then '比我大的'
                         when (SUBSTR(BIRTHDAY,0,4)  < '1970' and SUBSTR(BIRTHDAY,0,4)  > '1960')  then '比我大的2'
                      else '比我小的' end;
          7.替換field里面的值
          update tbl_lib_question set title=trim(Substr(title,Instr(title,'.',1,1)+1)) where type=4 and  id >2600

          posted on 2007-04-29 15:14 強(qiáng)強(qiáng) 閱讀(380) 評(píng)論(0)  編輯  收藏 所屬分類(lèi): Oracle數(shù)據(jù)庫(kù)

          主站蜘蛛池模板: 固镇县| 丹棱县| 吴忠市| 女性| 洛扎县| 舞钢市| 太白县| 峡江县| 合水县| 昂仁县| 沽源县| 延边| 娱乐| 偏关县| 霍州市| 兰西县| 大化| 盐津县| 全州县| 南丹县| 淮北市| 鸡泽县| 和硕县| 兴文县| 广饶县| 阜康市| 金湖县| 特克斯县| 孟津县| 行唐县| 上高县| 红原县| 通道| 浦东新区| 西林县| 滦平县| 和顺县| 永吉县| 长兴县| 泰安市| 无为县|