最近用到的oracle 查询语句，留个记录
//查询一个特定的时间段内、特定部门的、特定的action 的总的操作文档总数目
SELECT TO_CHAR(PROCTIME,'YYYY-MM-DD') AS TIME ,LOGINFO.ACTION ,COUNT(*) AS LOADCOUNT
FROM LOGINFO
WHERE ACTION =1 AND (TO_CHAR(PROCTIME,'YYYY-MM-DD') BETWEEN '2010-11-11' AND '2011-05-10') AND LOGINFO.DOCID IN(
  SELECT MEDICALINFO.DOCID FROM MEDICALINFO WHERE MEDICALINFO.APPCODE='APPCODE001'
)
GROUP BY TO_CHAR(PROCTIME,'YYYY-MM-DD'),LOGINFO.ACTION
ORDER BY TO_CHAR(PROCTIME,'YYYY-MM-DD') ASC

//取数据的第3列
select * from (
select rownum id ,LOGINFO.* from LOGINFO
) tt
where tt.id=3

//更新表中的列 每隔3个一次循环
UPDATE MEDICALINFO SET APPCODE='APPCODE003'
WHERE DOCID IN(
select DOCID from (
select mod(rownum,3) id ,MEDICALINFO.* from MEDICALINFO
) tt
where tt.id=0
)

//根据一个特定的日期和部门 和 一个特定的action 查询action对应的文档总数
SELECT TO_CHAR(PROCTIME,'YYYY-MM-DD HH24') AS TIME ,LOGINFO.ACTION ,COUNT(*) AS LOADCOUNT
FROM LOGINFO
WHERE ACTION =1 AND (TO_CHAR(PROCTIME,'YYYY-MM-DD') = '2011-05-09') AND LOGINFO.DOCID IN(
  SELECT MEDICALINFO.DOCID FROM MEDICALINFO WHERE MEDICALINFO.APPCODE='APPCODE001'
)
GROUP BY TO_CHAR(PROCTIME,'YYYY-MM-DD HH24'),LOGINFO.ACTION
ORDER BY TO_CHAR(PROCTIME,'YYYY-MM-DD HH24') ASC