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．三個月內登錄過的用戶列表
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.刪除最大的重復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 結果
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