取得某月的最后一周的星期一和星期天:
select TRUNC(LAST_DAY(TO_DATE('2013-09', 'YYYY-MM')),'IW') -7, TRUNC(LAST_DAY(TO_DATE('2013-11', 'YYYY-MM')),'IW') -1 FROM DUAL;
其中2013-09可以改变
取得某天的上一周
select (TRUNC(SYSDATE, 'IW') - 7),(TRUNC(SYSDATE, 'IW') - 1), TRUNC(SYSDATE, 'IW')from> AND T.COLUMN_NAME = T1.COLUMN_NAME
ORDER BY T.TABLE_NAME;
取得当前数据库编码:
SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET'
取得分组中某个字段最大的记录:
第一种方法:
SELECT DUMMY_QTY, INTRANSIT_QTY, ZONE_ID, AGENCY_ID, CREATED_ON
FROM A
WHERE A.CREATED_ON IN (SELECT MAX(CREATED_ON)
FROM A B
WHERE B.ZONE_ID = ZONE_ID
AND B.AGENCY_ID = AGENCY_ID
GROUP BY B.ZONE_ID,B.AGENCY_ID)
第二种方法,用分析函数实现:
SELECT DUMMY_QTY, INTRANSIT_QTY, ZONE_ID, AGENCY_ID, CREATED_ON
FROM (SELECT A.*,
ROW_NUMBER() OVER(PARTITION BY ZONE_ID, AGENCY_ID ORDER BY CREATED_ON DESC) RN
FROM A) B
WHERE B.RN = 1
第二种实现方法看执行计划用时比第一种方法少