Oracle SQL

SQL模板

• 查询详情

  SELECT c.ID,c.STATUS,c.CERT_NUMBER,u."NAME" OWNER,
  (SELECT u.NAME FROM TB_CERT c,TB_USER_INFO u WHERE c.APPLY_USER_ID=u.ID AND c."ID"=57) APPLYER,
  (SELECT u.NAME FROM TB_CERT c,TB_USER_INFO u WHERE c.AUTH_USER_ID=u.ID AND c."ID"=57) AUTHUSER 
  FROM TB_CERT c,TB_USER_INFO u 
  WHERE c.USER_ID=u.ID AND c."ID"=57
  

• Oracle 时间戳按天统计

  select to_char(DATA_IN_TIME/(1000*60*60*24)+to_date('1970-01-01 08:00:00','YYYY-MM-DD HH:MI:SS') ,'yyyy-MM-dd') TIME ,count(1) COUNT
          from TB_APP
          where DATA_IN_TIME >=#{beginTime} AND DATA_IN_TIME <=#{endTime}
          GROUP BY to_char(DATA_IN_TIME/(1000*60*60*24)+to_date('1970-01-01 08:00:00','YYYY-MM-DD HH:MI:SS') ,'yyyy-MM-dd')
  

• Oracle 时间戳统计当日新增

  SELECT COUNT(1) COUNT FROM TB_CERT WHERE TO_CHAR(DATA_IN_TIME/(1000*60*60*24)+to_date('1970-01-01 08:00:00','YYYY-MM-DD HH:MI:SS') ,'YYYY-MM-DD')=TO_CHAR(SYSDATE-1,'YYYY-MM-DD');(昨天的SYSDATE-1)
  

• Oracle数据库 根据时间戳按天进行数据统计

//DATA_IN_TIME为时间戳类型的时间
<select id="queryCountByTime"resultType="返回的实体">

        select to_char(DATA_IN_TIME/(1000*60*60*24)+to_date('1970-01-01 08:00:00','YYYY-MM-DD HH:MI:SS') ,'yyyy-MM-dd') TIME ,count(1) COUNT
        from TB_APP
        where DATA_IN_TIME &gt;=#{beginTime} AND DATA_IN_TIME &lt;=#{endTime}
        GROUP BY to_char(DATA_IN_TIME/(1000*60*60*24)+to_date('1970-01-01 08:00:00','YYYY-MM-DD HH:MI:SS') ,'yyyy-MM-dd')

</select>