常用内置函数

Oracle

-- greatest :两列中取更晚的时间
select greatest(time1,time2) as time from Test; 

-- concat :字符串拼接,只支持两个参数,Mysql为三个
select * from Test where name like concat('%','3');--可用 || 代替

-- replace :字符串替换,三个参数
select replace(id,'1','2') from Test;--id中的1替换为2

-- ifnull :若为空,替换为
select ifnull(id,'unknown') from Test;--id中的null替换为unknown

-- instr :根据in排序
SELECT * FROM Test  where id in  ('1','2') ORDER BY instr('1,2',id );

-- start with * onnect by * :树结构遍历
select * from TestA start with id='1' connect by pid = prior id ;

分组后按某个字段取出其中一行数据

SELECT * FROM (
 	SELECT TYPE,CREATE_TIME,ROW_NUMBER() OVER (PARTITION BY TYPE ORDER BY CREATE_TIME DESC) rn
 	FROM TABLE1 ) t WHERE t.rn = 1 -- 根据type分组然后取出每组create_time最晚的那条数据