首页统计脚本

--统计表
SELECT * FROM T_SG_SJDJTJ;
--数据表
SELECT * FROM v_dataservice_ext T WHERE T.DATA_TYPE='0';

--从描述信息核对(统计表有，业务表没有)
SELECT * FROM T_SG_SJDJTJ T WHERE T.MSXX
NOT IN(
SELECT S.DATA_NAME FROM v_dataservice_ext S WHERE S.DATA_TYPE='0'
);

--从表名核对
SELECT t.*,t.rowid FROM T_SG_SJDJTJ T WHERE T.YWB
NOT IN(
SELECT S.TABLE_NAME FROM v_dataservice_ext S WHERE S.DATA_TYPE='0'
);


--综合核对
SELECT M.TABLE_NAME,N.YWB,M.UNIT_NAME,N.YWDW FROM v_dataservice_ext M, 
(
SELECT T.MSXX,T.YWB,T.YWDW FROM T_SG_SJDJTJ T WHERE T.YWB
NOT IN(
SELECT S.TABLE_NAME FROM
v_dataservice_ext S, T_SG_SJDJTJ T 
WHERE 
S.DATA_TYPE='0'
AND S.DATA_NAME=T.MSXX
AND S.UNIT_NAME=T.YWDW
--AND S.TABLE_NAME<>T.YWB
))N WHERE M.DATA_NAME=N.MSXX AND M.DATA_TYPE='0';

--本周更新数据
SELECT * FROM T_SG_SJDJTJ  S
WHERE S.MSXX
NOT IN(
SELECT T.DATA_NAME FROM  T_SJTJ T
);

--月度统计
SELECT T.UNIT_NAME,T.DATA_NAME,T.DATA_METHODS,T.DATA_TOTAL,S.SL,decode(T.SYNC_MOTHODS,'0','增量','全量') methods
FROM DATA_REPORT_EXT T,T_SG_SJDJTJ S
 WHERE (T.TABLE_NAME=S.YWB OR T.TABLE_NAME=S.YWB||'_IN')
 AND T.DATA_TYPE='0'
 AND T.DISPLAY='1'
 ORDER BY T.UNIT_NAME;

--最近对接数据
SELECT t.UNIT_NAME,
       t.DATA_NAME,
       t.TABLE_NAME,
       t.CREATE_DATE,
       t.SYNC_MOTHODS
  FROM v_dataservice_ext t
 where T.DATA_TYPE = '0'
   AND TO_DATE(T.CREATE_DATE, 'yyyy-mm-dd hh24:mi:ss') >
       to_date('20160601', 'yyyymmdd')
 order by t.UNIT_NAME;


SELECT t.ywdw,t.msxx,t.ywb,t.rhjksj
  FROM t_sg_sjdjtj T
 WHERE TO_DATE(T.RHJKSJ, 'yyyymmddhh24miss') >
       to_date('20160601', 'yyyymmdd');