1.concat:合并字符串,rpad,substr:拆分字符串(取特定长度的字段),子查询综合应用;
select tree_code,concat(rpad(substr(tree_code,1,4),5,','),concat(rpad(substr(tree_code,5,4),5,','),
concat(rpad(substr(tree_code,9,4),5,','),substr(tree_code,13,4))))
from sa_dept_dict where dept_id=( select dept_id from sa_staff_role where user_id = 20235)
过程:先拆分字符串,再在每个字符串后加上“,”,再回并字段串;
运行结果:
2。oracle的rownum特殊使用;
select * from (select b.*,rownum as rowNumber from (
select ID,INPUT_TIME,TITLE from news_info order by INPUT_TIME desc) b) c where rowNumber<=3
过程:
运行结果:巧妙使用rownum,使其按照 INPUT_TIME (时间)排序得到最新三打数据;