ORACLE SQL常用函数【自己总结】

ORACLE SQL常用函数

1. EXTRACT(YEAR FROM RQ) as y, EXTRACT(MONTH FROM RQ) as m, 获取日期中年或月

2. Select to_char(last_day(sysdate), 'dd') from dual; 获取月份的天数

2. FLOOR(sysdate-to_date('2010-1-1','yyyy-MM-dd')) 获取两个日期之间的天数

3. CONCAT(y,m)

4. =TEXT(INT(("2011-7-1"-"2011-1-1")*RAND())+"2011-1-1","e-m-d") Excel 如何随机生成日期-时间

5. 如何查找出数据库表中重复的数据

a>--找出重复人员姓名 SELECT person_id,sapbh,xm,org_name,dict_gwmc FROM view_person WHERE xm IN (SELECT xm FROM person_account GROUP BY xm HAVING COUNT(xm) > 1) ORDER BY xm;

b>--找出重复的数据 SELECT pzh FROM tb_device WHERE pzh IN (SELECT pzh FROM tb_device GROUP BY pzh HAVING COUNT(pzh) > 1) ORDER BY pzh;

c>--找出重复的编码 SELECT code FROM sys_dict WHERE code IN (SELECT code FROM sys_dict GROUP BY code HAVING COUNT(code) > 1) ORDER BY code;

6. last_value

select ATTENDENCE_ID, PERSON_ID,KQRQ,KQLX,PROJECT_ID,ORG_ID,

last_value(kqrq) over (partition by person_id,project_id order by kqrq desc rows between current row and 1 following)

from PERSON_ATTENDENCE

--10. SQL学习语句补充

--------------------------------------------------------------------------

--select t.*,rank() over(partition by device_id order by device_id) maxdeviceid from tb_device_config t

--UPDATE tb_device_driver SET RQ=TRUNC(RQ)

--sum(xsgls) over(partition by device_id order by jsrq rows between unbounded preceding and 0 preceding) as ljxsgls,

--------------------------------------------------------------------------