SELECT * FROM gh_qyxx q ORDER BY q.drrq DESC
--1169897
select * from gh_qyxx q where q.drrq <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss') and q.drrq>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
SELECT * FROM gh_dsqyxx ds WHERE ds.qybm='A5558379'
SELECT count(*),ds.nsrzt FROM gh_dsqyxx ds group by ds.nsrzt
select count(*),q.nsrztmc from gh_qyxx q where
q.drrq <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss')
and q.drrq>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
group by q.nsrztmc
create view gh_qy_view as
select * from gh_qyxx q where
q.drrq <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss')
and q.drrq>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
and q.nsrztmc='正常'
SELECT count(*) FROM gh_qy_view
select COUNT(*),q.istc from gh_qyxx q where q.drrq <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss') and q.drrq>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
GROUP BY q.istc
SELECT * FROM gh_hs q where q.czsj <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss') and q.czsj>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
--1169897
SELECT * FROM gh_hs_ls q where q.czsj <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss') and q.czsj>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
CREATE VIEW ab AS
--1 :1169915
SELECT am.jhzt,COUNT(*) FROM (
select * from gh_qyxx q,gh_qyghgx gx WHERE gx.qybm=q.qybm AND q.drrq <= to_date('2017-10-24 00:00:00','yyyy-mm-dd hh24:mi:ss') and q.drrq>=to_date('2017-10-23 00:00:00','yyyy-mm-dd hh24:mi:ss')
AND q.istc='1' AND q.isxz='1' ) am GROUP BY am.jhzt
---1、地税状态（税务登记状态字段）：证件失效企业、停业企业、注销、非正常注销企业
----1706298
----3883730
select t.* ，qyghgx.GHDM,
qyghgx.GHMC,
qyghgx.GHLX,
qyghgx.ZHLX,
qyghgx.QYGHRS,
qyghgx.JHZT,
qyghgx.JHSJ,
qyghgx.SHSJ,
qyghgx.JFLCBL,
qyghgx.GHDM1,
qyghgx.BL1,
qyghgx.GHDM2,
qyghgx.BL2,
qyghgx.GHDM3,
qyghgx.BL3,
qyghgx.GHDM4,
qyghgx.BL4,
qyghgx.GHDM5,
qyghgx.BL5,
qyghgx.GHDM6,
qyghgx.BL6,
qyghgx.QYJC,
qyghgx.sffq
from GH_QYXX t
left join GH_QYGHGX qyghgx
on t.qybm = qyghgx.qybm
--5822532
left join GH_ZD z on qyghgx.jhzt = z.zddm
where (t.nsrztmc = '证件失效' or t.nsrztmc like '%停业%' or
t.nsrztmc = '注销' or t.nsrztmc like '%非正常%')
and z.zdlx = 'JHZT' and ORDER BY t.drrq desc;
SELECT * FROM gh_qyxx q,gh_qyghgx g WHERE q.qybm=g.qybm and q.qybm='A6174511'
---2、是否是京外经营 28未建会-不应代收-京外经营
---2.1、所属街乡代码是否为空，不为空，则根据scjxdm来判断是否为京外经营
--0
select t.* ，qyghgx.GHDM,
qyghgx.GHMC,
qyghgx.GHLX,
qyghgx.ZHLX,
qyghgx.QYGHRS,
qyghgx.JHZT,
qyghgx.JHSJ,
qyghgx.SHSJ,
qyghgx.JFLCBL,
qyghgx.GHDM1,
qyghgx.BL1,
qyghgx.GHDM2,
qyghgx.BL2,
qyghgx.GHDM3,
qyghgx.BL3,
qyghgx.GHDM4,
qyghgx.BL4,
qyghgx.GHDM5,
qyghgx.BL5,
qyghgx.GHDM6,
qyghgx.BL6,
qyghgx.QYJC,
qyghgx.sffq
from gh_qy_view t
left join GH_QYGHGX qyghgx
on t.qybm = qyghgx.qybm
where t.ISTCCONDITION = '0'
and substr(t.scjxdm, 0, 2) != '11';
---2.2、如果scjxdm是空的，则根据经营地址来判断是否为京外经营
--0
select t.* ，qyghgx.GHDM,
qyghgx.GHMC,
qyghgx.GHLX,
qyghgx.ZHLX,
qyghgx.QYGHRS,
qyghgx.JHZT,
qyghgx.JHSJ,
qyghgx.SHSJ,
qyghgx.JFLCBL,
qyghgx.GHDM1,
qyghgx.BL1,
qyghgx.GHDM2,
qyghgx.BL2,
qyghgx.GHDM3,
qyghgx.BL3,
qyghgx.GHDM4,
qyghgx.BL4,
qyghgx.GHDM5,
qyghgx.BL5,
qyghgx.GHDM6,
qyghgx.BL6,
qyghgx.QYJC,
qyghgx.sffq
from gh_qy_view t
left join GH_QYGHGX qyghgx
on t.qybm = qyghgx.qybm
where t.ISTCCONDITION = '0'
and t.scjxdm is null;
--3、空壳单位
---3.1已经建会的单位
--0
select qy.*
from (select t.* ，qyghgx.GHDM,
qyghgx.GHMC,
qyghgx.GHLX,
qyghgx.ZHLX,
qyghgx.QYGHRS,
qyghgx.JHZT,
qyghgx.JHSJ,
qyghgx.SHSJ,
qyghgx.JFLCBL,
qyghgx.GHDM1,
qyghgx.BL1,
qyghgx.GHDM2,
qyghgx.BL2,
qyghgx.GHDM3,
qyghgx.BL3,
qyghgx.GHDM4,
qyghgx.BL4,
qyghgx.GHDM5,
qyghgx.BL5,
qyghgx.GHDM6,
qyghgx.BL6,
qyghgx.QYJC,
qyghgx.sffq
from gh_qy_view t
left join GH_QYGHGX qyghgx
on t.qybm = qyghgx.qybm
where t.ISTCCONDITION = '0'
and qyghgx.jhzt in ('0',
'1',
'2',
'3',
'4',
'5',
'6',
'7',
'8',
'9',
'10',
'11',
'12',
'13',
'14',
'15',
'16',
'17')) qy
left join (select qybm,
sum(zgrs) / count(zgrs) as zgrs,
sum(gzze) / count(gzze) as gzze
from gh_qygsgz
where (skssy = to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-1),
'mm')) and
skssn = to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-1),
'yyyy')))
or (skssy = to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-2),
'mm')) and
skssn = to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-2),
'yyyy')))
or (skssy = to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-3),
'mm')) and
skssn = to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-3),
'yyyy')))
group by qybm) gsgz
on qy.qybm = gsgz.qybm
where gsgz.zgrs < 3
or gsgz.gzze = 0
or gsgz.qybm is NULL ;
update gh_qygsgz tt
set tt.skssy = substr(tt.skssy, 2, 1)
WHERE tt.skssy like '0%';
SELECT count(*),tt.skssy,tt.skssn,substr(tt.skssy,2,5) FROM gh_dsgsgz tt WHERE tt.skssy like '0%' group by tt.skssy,tt.skssn
SELECT to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-3),
'yyyy')) FROM dual;
SELECT to_number(to_char(add_months(to_date('2017/05/01','yyyy/MM/dd'),
-1),
'mm')) FROM dual;
update gh_dsgsgz tt set tt.skssy =substr(tt.skssy,2,5) WHERE tt.skssy like '0%'
and( tt.shxydm=(SELECT gz.shxydm FROM gh_dsgsgz gz) or tt.nsrsbh=(SELECT gz.nsrsbh FROM gh_dsgsgz gz))
truncate table gh_qygsgz ;
truncate table gh_dsgsgz ;
--2016：1-6月数据没有 2017:1-4有数据
SELECT count(*),gz.skssy,gz.skssn FROM gh_qygsgz gz WHERE gz.skssy like '0%' group by gz.skssy,gz.skssn
SELECT * FROM gh_dsgsgz gz
SELECT * FROM gh_jc_yg y ,gh_jc_ygbmgx g WHERE y.ygdm=g.ygdm and y.yhm='1'
insert into GH_HS
(YWLSDM,
QYBM,
HZLX,
LXR,
LXDH,
KHH,
KHZH,
KHMC,
CJHSZT,
JHZTDM,
QYGHMC,
QYGHRS,
SJJYDZ,
JHSJ,
SHSJ,
CZRYDM,
CZSJ,
ZGGHDM,
ZHLX,
QYMC,
SFZC,
JFLCBL,
BL1,
BL2,
BL3,
BL4,
BL5,
BL6,
ISFIRST,
XEZFH)
SELECT
q.ywlsdm,
q.qybm,
q.HZLX,
q.LXR,
q.LXDH,
q.KHH,
q.KHZH,
q.KHMC,
q.CJHSZT,
q.JHZTDM,
q.QYGHMC,
q.QYGHRS,
q.SJJYDZ,
q.JHSJ,
q.SHSJ,
q.CZRYDM,
q.CZSJ,
q.ZGGHDM,
q.ZHLX,
q.QYMC,
q.SFZC,
q.JFLCBL,
q.BL1,
q.BL2,
q.BL3,
q.BL4,
q.BL5,
q.BL6,
q.ISFIRST,
q.XEZFH
FROM gh_hs_ls q where q.czsj <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss') and q.czsj>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
delete FROM gh_hs_ls q where q.czsj <= to_date('2017-10-26 00:00:00','yyyy-mm-dd hh24:mi:ss') and q.czsj>=to_date('2017-10-25 00:00:00','yyyy-mm-dd hh24:mi:ss')
SELECT * FROM gh_jc_yg yg WHERE yg.yhm='110105101673749000'
------------------------------------------------------------------------------------
----3.2未建会的单位
--270414
select qy.*, gsgz.zgrs, gsgz.gzze
from (select t.* ，qyghgx.GHDM,
qyghgx.GHMC,
qyghgx.GHLX,
qyghgx.ZHLX,
qyghgx.QYGHRS,
qyghgx.JHZT,
qyghgx.JHSJ,
qyghgx.SHSJ,
qyghgx.JFLCBL,
qyghgx.GHDM1,
qyghgx.BL1,
qyghgx.GHDM2,
qyghgx.BL2,
qyghgx.GHDM3,
qyghgx.BL3,
qyghgx.GHDM4,
qyghgx.BL4,
qyghgx.GHDM5,
qyghgx.BL5,
qyghgx.GHDM6,
qyghgx.BL6,
qyghgx.QYJC,
qyghgx.sffq
from GH_QYXX t
left join GH_QYGHGX qyghgx
on t.qybm = qyghgx.qybm
where t.ISTCCONDITION = '0'
and qyghgx.jhzt in ('18',
'19',
'20',
'21',
'22',
'23',
'24',
'25' ， '28')) qy
left join (select qybm, zgrs, gzze
from gh_qygsgz
where skssy = to_number(to_char(add_months(to_date(sysdate),
-1),
'mm'))
and skssn = to_number(to_char(add_months(to_date(sysdate),
-1),
'yyyy'))) gsgz
on qy.qybm = gsgz.qybm
where gsgz.zgrs < 3
or gsgz.gzze = 0
or gsgz.qybm is null;