--MARTIN SQL-------------
--alter table CEN_CHECK_MEMBER nologging;
--alter table CEN_CHECK_MEMBER logging;
aselect LABOR_FORCE from cen_check_member where card_id = '620102195706280910'
-一、根据身份证号处理数据 普通 0 无 2 弱或半1
UPDATE CEN_CHECK_MEMBER m
SET m.LABOR_FORCE = CASE
WHEN EXTRACT(YEAR FROM SYSDATE) - SUBSTR(m.card_id, 7, 4) < 16 THEN '2'
-- 16岁以下（不包含16岁）
WHEN EXTRACT(YEAR FROM SYSDATE) - SUBSTR(m.card_id, 7, 4) BETWEEN 16 AND 59 THEN '0' --16-60岁（包含16岁，不包含60岁） WHEN EXTRACT(YEAR FROM SYSDATE) - SUBSTR(m.card_id, 7, 4) BETWEEN 60 AND 69 THEN '1' --60-70岁（包含60岁，不包含70岁） ELSE '2'
-- 70岁及以上 sex
END
WHERE LENGTH(m.card_id) = 18
AND m.card_id IS NOT NULL
AND REGEXP_LIKE(m.card_id, '^\d{18}$') -- 确保card_id是有效的18位数字
AND SUBSTR(m.card_id, 7, 4) BETWEEN '1900' AND '2099' -- 出生年份在1900年到2099年之间
AND m.aaa001 IN sys据
MERGE INTO CEN_CHECK_MEMBER t1
USING (
SELECT distinct CARD_ID
FROM CEN_CHECK_MEMBER
WHERE DISABILITY_LEVEL IN ('3', '4')
AND EXTRACT(YEAR FROM SYSDATE) - SUBSTR(CARD_ID, 7, 4) BETWEEN 60 AND 69 AND REGEXP_LIKE(card_id, '^\d{18}$') and card_id ='620102195706280910'
) t2
ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.CARD_ID, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.LABOR_FORCE = '2';
--三、比对医保数据 参保资助金额、个人自负金额都不为0
MERGE INTO CEN_CHECK_MEMBER t1
USING (
SELECT distinct t.身份证号
FROM TBL_YBJ_20240830 t
WHERE t.参保资助金额>0 and t.个人自负金额>0
AND EXTRACT(YEAR FROM SYSDATE) - SUBSTR(身份证号, 7, 4) BETWEEN 60 AND 69 AND REGEXP_LIKE(身份证号, '^\d{18}$') and 身份证号 in ('620102195706280910',
'620102196008177013',
'620102195608057029',
'620102196410287018',
'620102196309257025',
'620102195707157017',
'620102196409157021',
'620102196401057034',
'620102196003317013',
'620103196412233538',
'620103196103053517',
'620103195601124037',
'620103196007034025',
'620103196301304022',
'620103195702084425')
) t2
ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.身份证号, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.LABOR_FORCE = '2';
MERGE INTO CEN_CHECK_MEMBER t1
USING (
SELECT distinct t.身份证号
FROM TBL_YBJ_20240830 t
WHERE t.参保资助金额>0 and t.个人自负金额>0
AND EXTRACT(YEAR FROM SYSDATE) - SUBSTR(身份证号, 7, 4) BETWEEN 16 AND 59 AND REGEXP_LIKE(身份证号, '^\d{18}$')
) t2
ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.身份证号, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.LABOR_FORCE = '1';
select fn_checkidcard(card_id) aa from cen_check_member where aa =1
SELECT
card_id,,
CASE
WHEN TO_DATE(SUBSTR(card_id, 7, 4) || '-' || SUBSTR(card_id, 11, 2) || '-' || SUBSTR(card_id, 13, 2), 'YYYY-MM-DD')
BETWEEN TO_DATE('2009-06-30', 'YYYY-MM-DD') AND TO_DATE('2017-08-31', 'YYYY-MM-DD') THEN '1'
ELSE '0'
END AS IS_COMPULSORY_EDUCATION
FROM cen_check_member
WHERE LENGTH(card_id) = 18 and fn_checkidcard(card_id) =1 ;
UPDATE cen_check_member
SET IS_COMPULSORY_EDUCATION =CASE
WHEN TO_DATE(SUBSTR(card_id, 7, 4) || '-' || SUBSTR(card_id, 11, 2) || '-' || SUBSTR(card_id, 13, 2), 'YYYY-MM-DD')
BETWEEN TO_DATE('2009-06-30', 'YYYY-MM-DD') AND TO_DATE('2017-08-31', 'YYYY-MM-DD') THEN '1'
ELSE '0'
END AS IS_COMPULSORY_EDUCATION
WHERE fn_checkidcard(card_id) ='1'
UPDATE cen_check_member
SET IS_COMPULSORY_EDUCATION
select fn_checkidcard(card_id) as VIALD_CARD_ID,CARD_ID FROM CEN_CHECK_MEMBER) t where t.VIALD_CARD_ID =1 and
CASE
WHEN TO_DATE(SUBSTR(card_id, 7, 4) || '-' || SUBSTR(card_id, 11, 2) || '-' || SUBSTR(card_id, 13, 2), 'YYYY-MM-DD')
BETWEEN TO_DATE('2009-06-30', 'YYYY-MM-DD') AND TO_DATE('2017-08-31', 'YYYY-MM-DD') THEN '1'
ELSE '0'
END
) t2
ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.CARD_ID, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.LABOR_FORCE = '2'; update cen_check_member t set t.IS_COMPULSORY_EDUCATION = CASE WHEN fn_checkidcard(card_id) = 1 and TO_DATE(SUBSTR(card_id, 7, 4) || '-' || SUBSTR(card_id, 11, 2) || '-' || SUBSTR(card_id, 13, 2), 'YYYY-MM-DD') BETWEEN TO_DATE('2009-06-30', 'YYYY-MM-DD') AND TO_DATE('2017-08-31', 'YYYY-MM-DD') THEN '1' ELSE '0' END where LENGTH(t.card_id) = 18; select * from cen_check_family t where exists (select 1 from cen_check_family_history f where t.card_id=f.card_id and f.card_id='622425197402048014') ; select count(1) from cen_check_member where is_delete = '8' ;
delete from CEN_PERSON_FAMILY where id in (
SELECT id FROM ( SELECT id, person_id, family_id, create_time, state, ROW_NUMBER() OVER (PARTITION BY family_id ORDER BY TO_DATE(create_time, 'YYYY-MM-DD HH24:MI:SS') DESC) AS rn FROM CEN_PERSON_FAMILY ) t WHERE rn > 1);
create table c_c_family_history_20240909 as select * from cen_check_family_history;
create table c_c_member_history_20240909 as select * from cen_check_member_history; ;
--- 删除历史表中重复的户数据
delete from cen_check_family_history t where exists (select 1 from cen_check_family f where t.card_id=f.card_id);
--- 删除历史表中重复的成员数据
delete from cen_check_member_history t where exists (select 1 from cen_check_member f where t.card_id=f.card_id);
select t.* from cen_check_member_history t where exists (select 1 from cen_check_member f where t.card_id=f.card_id);
select t.* from cen_check_member_history t where exists (select 1 from cen_check_member f where t.mem_number=f.mem_number);
delete from cen_check_member_history t where exists (select 1 from cen_check_member f where t.card_id=f.card_id and t.aaa001=f.aaa001);
create table cen_check_member_history0912 as select * from cen_check_member_history;--130424198902241224 62060201500962005153399
--- 将户表的家庭成员数赋值
MERGE INTO cen_check_family t1
USING (
SELECT aaa001,count(aaa001) as count_num
FROM CEN_CHECK_MEMBER
WHERE is_delete ='0' group by aaa001 ) t2
ON (t1.aaa001 = t2.aaa001)
WHEN MATCHED THEN
UPDATE SET t1.family_size = t2.count_num , t1.update_datetime='2024-09-09 11:00:00'; commit;
--- 查询有人无户的数据 有109w
---select count(1) from cen_check_member where is_delete = '8' ;
---将成员表中状态为8的数据新增到历史表中
MERGE INTO cen_check_member_history t
USING (
SELECT * FROM cen_check_member t1 WHERE t1.is_delete = '8'
) t2
ON (t.card_id = t2.card_id and t.mem_number=t2.mem_number ) -- card_id是匹配字段
WHEN NOT MATCHED THEN
INSERT (t.mem_number, t.aaa001, t.mem_name, t.mem_sex, t.card_id,t.Labor_Force,t.labor_cgreason,
t.relation_with_head,t.relation_with_head_other, t.is_pre_mem, t.is_delete, t.create_datetime,
t.status, t.update_datetime, t.recovery_type,t.zhycqtyy ) -- 列出cen_check_member_history的列名
VALUES (t2.mem_number, t2.aaa001, t2.mem_name, t2.mem_sex, t2.card_id,t2.Labor_Force,t2.labor_cgreason,
t2.relation_with_head,t2.relation_with_head_other, t2.is_pre_mem, t2.is_delete, t2.create_datetime,
t2.status, '2024-09-09 12:00:00', '2','JZPCZD006BF16D4CE1547E64141BLX07');
--- 删除成员表中状态为8 的数据
delete from cen_check_member where is_delete = '8' ;
expdp gstpgg/Gstpgg_2021@JZFPDB TABLES=cen_check_family
directory=D:\oraclebackupdata dumpfile=cen_check_family20240909.dmp logfile=cen_check_family_backup.log;
expdp gstpgg/Gstpgg_2021@JZFPDB TABLES=cen_check_member
directory=D:\oraclebackupdata dumpfile=cen_check_member20240909.dmp logfile=cen_check_member_backup.log;
select card_id from cen_check_member_history group by card_id having count(1)>1;
select card_id from cen_check_member where is_delete ='0' group by card_id having count(1)>1;
select * from cen_check_member where card_id='622426199007150516';
delete from CEN_CHECK_MEMBER t
where (t.card_id,t.status) in
(select a.card_id,a.status from CEN_CHECK_MEMBER a where a.is_delete='0' group by a.card_id,a.status having count() > 1)and rowid not in (select min(rowid) from CEN_CHECK_MEMBER s where s.is_delete='0' group by s.card_id,s.status having count() > 1);
select * from NYTT_XD ; SELECT XZ FROM NYTT_XD GROUP BY XZ;
SELECT * FROM nytt_xd WHERE XZ IN ('6','7','8','9'); SELECT * FROM NYTT_XD WHERE XZ IN ('1','2','3','4','5') and xd is null and ; SELECT * FROM NYTT_XD WHERE XZ IN ('8'); SELECT * FROM nhsj_new WHERE XZ IN ('9');
MERGE INTO CEN_CHECK_MEMBER t1
USING (
select sfzhm as card_id from nhsj_new WHERE SFZX = '01'
) t2
ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.CARD_ID, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.LABOR_FORCE = '2'
WHERE EXTRACT(YEAR FROM TO_DATE(SUBSTR(t1.card_id, 7, 4) || '-' || SUBSTR(t1.card_id, 11, 2) || '-' || SUBSTR(t1.card_id, 13, 2), 'YYYY-MM-DD'))
BETWEEN 16 AND 59 ;
MERGE INTO CEN_CHECK_MEMBER t1
USING (
select sfzh as card_id from NYTT_XD where XZ IN ('1','2','3','4','5')
) t2
ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.CARD_ID, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.LABOR_FORCE = '2'
WHERE EXTRACT(YEAR FROM TO_DATE(SUBSTR(t1.card_id, 7, 4) || '-' || SUBSTR(t1.card_id, 11, 2) || '-' || SUBSTR(t1.card_id, 13, 2), 'YYYY-MM-DD'))
BETWEEN 16 AND 59 ;
select f.AAC191 from cen_check_family f where card_id in ('622625195205103311','622625196607073316') and (f.FAM_TYPE in ('01','03') or f.AAC191 in ('01','02','03')) ; SELECT * FROM ( SELECT TMP.*, ROWNUM ROW_ID FROM ( SELECT f.AAA001, f.AZC005, f.AZC006, f.FAM_NAME, f.FAM_SEX, f.FAMILY_ADDRESS, f.CARD_ID, f.FAMILY_SIZE, f.PHONE_NUMBER, f.FAM_TYPE, f.IS_DELETE, f.STATUS, f.VILLAGE_GROUP FROM CEN_CHECK_FAMILY f WHERE f.IS_DELETE = '0' and f.CARD_ID in ('622625195205103311','622625196607073316') and f.AZC005 = '621224007027' and (f.FAM_TYPE in ('01','03') or f.AAC191 in ('01','02','03')) ) TMP WHERE ROWNUM <=10) WHERE ROW_ID > 0;
-- 89514 906
select f.FAM_TYPE,f.aac191 from cen_check_family f where f.FAM_TYPE not in ('01','03') or f.AAC191 not in ('01','02','03');
select count(1) from cen_check_family f where f.FAM_TYPE not in ('01','03') or f.AAC191 not in ('01','02','03');
select distinct t.监测对象类别 from tbl_tphjcdx20240825 t where t.监测对象类别 is not null;
select count(1) from tbl_tphjcdx20240825 t where t.监测对象类别 is not null and t.与户主关系='01';
MERGE INTO cen_check_family t1
USING (
SELECT 证件号码, 监测对象类别
FROM tbl_tphjcdx20240825
WHERE 监测对象类别 IS NOT NULL
) t2
ON (SUBSTR(t1.card_id, 1, 18) = SUBSTR(t2.证件号码, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.aac191 = CASE WHEN t1.aac191 IS NULL THEN t2.监测对象类别 ELSE t1.aac191 END;
select * from (select card_id from cen_check_family f where f.FAM_TYPE = '02' and f.AAC191 is null)
t1
where exists (select 1 from tbl_tphjcdx20240825 t where t.监测对象类别 is not null and SUBSTR(t1.card_id, 1, 18) = SUBSTR(t.证件号码, 1, 18) )
;
alter table cen_check_family nologging;
alter table cen_check_family logging;
---更新户表的AAC191
UPDATE cen_check_family f
SET f.AAC191 = (
SELECT t.监测对象类别 -- 或者使用 MAX，取决于你的需求
FROM tbl_tphjcdx20240825 t
WHERE SUBSTR(f.card_id, 1, 18) = SUBSTR(t.证件号码, 1, 18)
AND t.监测对象类别 IS NOT NULL
)
WHERE EXISTS (
SELECT 1
FROM tbl_tphjcdx20240825 t
WHERE SUBSTR(f.card_id, 1, 18) = SUBSTR(t.证件号码, 1, 18)
AND t.监测对象类别 IS NOT NULL
)
AND f.FAM_TYPE = '02'
AND f.AAC191 IS NULL;
select * from cen_check_family where aac191 is null and fam_type='02';--130424198902241224 62060201500962005153399
select * from cen_check_family where aaa001 ='62082600901562005075223'
select card_id from cen_check_family group by card_id having count(1)>1;
select card_id from cen_check_family where status ='3' group by card_id having count(1)>1;
select * from cen_check_family where card_id ='620121195209155333';
select * from cen_check_member
where card_id ='640322199508281910';6e43199f166d434f9d61be3d3a1abb3a
select * from cen_check_family_history where card_id ='';
select * from cen_check_member_history where card_id ='';
select t.card_id from cen_check_family t left join cen_check_member f
on t.card_id = f.card_id group by t.card_id having(1)>1;
select tt.* from (select * from CEN_CHECK_FAMILY t
where t.card_id in
(select a.card_id from CEN_CHECK_FAMILY a group by a.card_id having count(*) > 1))
tt where exists (select 1 from cen_check_member f on tt.aaa001 = f.aaa001);
86200022435954 09f84544670a4c838571a663306f82dc
select tt.*
FROM (
SELECT *
FROM CEN_CHECK_FAMILY t
WHERE t.card_id IN (
SELECT a.card_id
FROM CEN_CHECK_FAMILY a
GROUP BY a.card_id
HAVING COUNT(*) > 1
)
) tt
WHERE EXISTS (
SELECT 1
FROM cen_check_member f
WHERE tt.aaa001 = f.aaa001
);
select a.card_id from CEN_CHECK_FAMILY_history a group by a.card_id having count() > 1;delete from cen_check_family m--left join (select a.card_id from CEN_CHECK_FAMILY a group by a.card_id having count() > 1) t
--on m.card_id = t.card_id
left join cen_check_member n
on m.card_id= n.card_id and m.aaa001=n.aaa001
where
n.aaa001 is null
---t.card_id is not null and
n.card_id is null order by m.card_id desc;
select a.card_id from CEN_CHECK_member a group by a.card_id having count(*) > 1;
select * from CEN_CHECK_FAMILY_history where
-- family_address is null;
card_id ='622421198811114819';
MERGE INTO cen_check_FAMILY_history t
USING (
select m.* from cen_check_family m
--left join (select a.card_id from CEN_CHECK_FAMILY a group by a.card_id having count(*) > 1) t
--on m.card_id = t.card_id
left join cen_check_member n
on m.card_id= n.card_id and m.aaa001=n.aaa001
where
n.aaa001 is null or n.card_id is null
) t2
ON (t.card_id = t2.card_id ) -- card_id是匹配字段
WHEN NOT MATCHED THEN
INSERT (t.AAA001,t.AZC001,t.AZC002,
t.AZC003,t.AZC004,t.AZC005,
t.AZC006,t.PROVINCE,t.CITY,
t.COUNTY,t.TOWN,t.VILLAGE,
t.VILLAGE_GROUP,t.FAM_NAME,t.FAM_SEX,
t.FAMILY_ADDRESS,t.CARD_ID,t.FAMILY_SIZE,
t.PNHOE_NUMBER,t.FAM_TYPE,t.AAC191,t.IS_CANCEL_RISK,
t.IS_VILLAGE_BUILD_FAM,t.RANGE_INCOME,t.IS_OVER_MIN_MONITOR_RANGE,
t.IS_EXIST_COMPLIANCE_OUT,t.IS_EXIST_SETTLEMENT,t.SALARY_INCOME,
t.PROPERTY_INCOME,t.TRANSFER_INCOME,t.MANAGEMENT_INCOME,
t.MANAGEMENT_OUTCOME,t.IS_COLLECTIVE_FAM,t.IS_EDU_OUT,
t.IS_JOIN_MEDICAL_INSURANCE,t.IS_SAFETY_HOUSING,t.IS_SATETY_WATER,
t.IS_LAND_CONTRACT,t.IS_EXIST_BACK_POOR_RISK,t.BACK_POOR_REASON,
t.IS_DELETE,t.STATUS,t.PRESET_DATETIME,
t.CANCEL_RISK_DATETIME,t.CREATE_BY,t.CREATE_DATETIME,
t.UPDATE_BY,t.UPDATE_DATETIME,t.BAK1,
t.BAK2,BAK3,t.AREA_CODE,t.zhxzqtyy) -- 列出cen_check_member_history的列名
VALUES (t2.AAA001,t2.AZC001,t2.AZC002,
t2.AZC003,t2.AZC004,t2.AZC005,
t2.AZC006,t2.PROVINCE,t2.CITY,
t2.COUNTY,t2.TOWN,t2.VILLAGE,
t2.VILLAGE_GROUP,t2.FAM_NAME,t2.FAM_SEX,
t2.FAMILY_ADDRESS,t2.CARD_ID,t2.FAMILY_SIZE,
'',t2.FAM_TYPE,t2.AAC191,t2.IS_CANCEL_RISK,
t2.IS_VILLAGE_BUILD_FAM,t2.RANGE_INCOME,t2.IS_OVER_MIN_MONITOR_RANGE,
t2.IS_EXIST_COMPLIANCE_OUT,t2.IS_EXIST_SETTLEMENT,t2.SALARY_INCOME,
t2.PROPERTY_INCOME,t2.TRANSFER_INCOME,t2.MANAGEMENT_INCOME,
t2.MANAGEMENT_OUTCOME,t2.IS_COLLECTIVE_FAM,t2.IS_EDU_OUT,
t2.IS_JOIN_MEDICAL_INSURANCE,t2.IS_SAFETY_HOUSING,t2.IS_SATETY_WATER,
t2.IS_LAND_CONTRACT,t2.IS_EXIST_BACK_POOR_RISK,t2.BACK_POOR_REASON,
t2.IS_DELETE,t2.STATUS,t2.PRESET_DATETIME,
t2.CANCEL_RISK_DATETIME,t2.CREATE_BY,t2.CREATE_DATETIME,
t2.UPDATE_BY,t2.UPDATE_DATETIME,t2.BAK1,
t2.BAK2,t2.BAK3,t2.azc005,'JZPCZD006BF16D4CE1547E64141BLX07');
--
delete from cen_check_family m where not exists (select 1 from cen_check_member n
where m.card_id =n.card_id);
MERGE INTO cen_check_member t1
USING (
SELECT *
FROM CEN_CHECK_family ) t2
ON (t1.card_id = t2.card_id)
WHEN MATCHED THEN
UPDATE SET t1.aaa001 = t2.aaa001;
MERGE INTO CEN_CHECK_MEMBER t1
USING (
select sfzhm as card_id from nhsj_new WHERE SFZX = '01'
) t2
ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.CARD_ID, 1, 18))
WHEN MATCHED THEN
UPDATE SET t1.LABOR_FORCE = '2'
WHERE EXTRACT(YEAR FROM TO_DATE(SUBSTR(t1.card_id, 7, 4) || '-' || SUBSTR(t1.card_id, 11, 2) || '-' || SUBSTR(t1.card_id, 13, 2), 'YYYY-MM-DD'))
BETWEEN 16 AND 59 ;
select EXTRACT(YEAR FROM TO_DATE(SUBSTR(t1.card_id, 7, 4) || '-' || SUBSTR(t1.card_id, 11, 2) || '-' || SUBSTR(t1.card_id, 13, 2), 'YYYY-MM-DD')) BETWEEN 16 AND 59 from CEN_CHECK_MEMBER t1 where LENGTH(t1.card_id) = 18 and fn_checkidcard(t1.card_id) =1
select * from CEN_CHECK_MEMBER t where t.card_id in (
SELECT card_id
FROM CEN_CHECK_MEMBER
where is_delete ='0'
GROUP BY card_id
HAVING COUNT(*)>1);
MERGE INTO CEN_CHECK_MEMBER t1 USING ( SELECT distinct CARD_ID FROM CEN_CHECK_MEMBER WHERE DISABILITY_LEVEL = '1' AND EXTRACT(YEAR FROM SYSDATE) - SUBSTR(CARD_ID, 7, 4) BETWEEN 16 AND 59 ) t2 ON (SUBSTR(t1.CARD_ID, 1, 18) = SUBSTR(t2.CARD_ID, 1, 18)) WHEN MATCHED THEN UPDATE SET t1.LABOR_FORCE = '2' WHERE EXTRACT(YEAR FROM SYSDATE) - SUBSTR(t1.card_id, 7, 4) BETWEEN 16 AND 59; select count() from cen_check_member t where t.labor_force = '2' ; MERGE INTO cen_check_family t USING (select * from CEN_CHECK_MEMBER_RM) r ON (t.card_id = r.CARD_ID) WHEN MATCHED THEN UPDATE SET t.FAM_NAME = r.MEM_NAME ,t.FAM_SEX = r.MEM_SEX; select t.,t.rowid from CEN_STUDY_DETERMINE t where t.area_code = '621227003006'; select t.area_code,t.name from CEN_STUDY_DETERMINE t group by t.area_code,t.name having count(1)>1; select t.,t.rowid from tbl_sys_client_info t; --统计分组后的排查户数 select * from cen_person_family where person_id in (select user_id from cen_survey_person where user_id in (select t.id from sys_user t where t.login_name like '621027012003pc%')); select * from cen_survey_person t where t.login_name ='' ; ---统计清查摸底全部数据 select card_id,fam_name,fam_type,aac191 from cen_check_family t where t.azc005 ='621027012003';--256+20+475=751 select fam_type,count(1) from cen_check_family t where t.azc005='621027012003' group by fam_type; select * from cen_check_family t where fam_type='02' and t.azc005='620171003020'; SELECT * FROM ( SELECT TMP., ROWNUM ROW_ID FROM ( SELECT f.AAA001, f.AZC005, f.AZC006, f.FAM_NAME, f.FAM_SEX, f.FAMILY_ADDRESS, f.CARD_ID, f.FAMILY_SIZE, f.PHONE_NUMBER, f.FAM_TYPE, f.aac191,f.IS_DELETE, f.STATUS, f.VILLAGE_GROUP FROM CEN_CHECK_FAMILY f WHERE f.IS_DELETE = '0' and f.AZC005 = '620171003020' and f.AAC191 in ('01','02','03') ) TMP WHERE ROWNUM <=1000) WHERE ROW_ID > 0 ;
SELECT a.family_id FROM (
select m.,m.rowid from cen_person_family m where person_id in (select user_id from cen_survey_person where user_id in(select t.id from sys_user t where t.login_name like '620802022019pc003%'))) agroup by a.family_id having count(1)>1;SELECT a.card_id FROM cen_person_family agroup by a.card_id having count(1)>1;select t.,rowid from cen_person_family t where card_id= '62112519720117501X';--00b6bff1f05f4a66af829775f76359cf 62112501101162005314854
select t.*,rowid from cen_check_family t where t.aaa001 ='62112501101162005314854';
update cen_person_family t set t.state=t.person_id where not exists (select 1 from cen_check_family f where t.family_id =f.aaa001 );
update cen_person_family t set t.person_id='' where not exists (select 1 from cen_check_family f where t.family_id =f.aaa001 );
drop table cen_person_family_state;
DROP TABLE IF EXISTS cen_person_family_state;
create table cen_person_family_bak as select * from cen_person_family where person_id='';
select count(1) from cen_person_family_state;
select count(1) from cen_person_family;
SELECT f.AAA001, f.AZC005, f.AZC006, f.FAM_NAME, f.CARD_ID, f.VILLAGE_GROUP,f.STATUS FROM CEN_CHECK_FAMILY f WHERE f.IS_DELETE = '0' and (f.FAM_TYPE in ('01','03') or f.AAC191 in ('01','02','03')) and f.AZC005 = '621027012003' --and f.status = '0' and exists (select 1 from CEN_PERSON_FAMILY where FAMILY_ID = f.AAA001) ORDER BY CREATE_DATETIME DESC
select a.aaa001,a.fam_name,a.fam_type,a.aac191,a.is_delete,a.azc005,b.family_id from (
select f.* from CEN_CHECK_FAMILY f where
f.IS_DELETE = '0' and (f.FAM_TYPE in ('01','03') or f.AAC191 in ('01','02','03'))
and f.AZC005 = '620802022019' ) a left join (
select m.family_id from cen_person_family m where person_id in (select user_id from cen_survey_person where user_id in
(select t.id from sys_user t where t.login_name like '620802022019pc00%'))
) b
on a.aaa001 = b.family_id where a.aaa001 is null ;
select m.* from cen_person_family m where m.village_group is not null;
select user_id from cen_survey_person where user_id in
(select t.id from sys_user t where t.login_name like '621027012003pc%');
select t.id from sys_user t where t.login_name like '621027012003pc%' and t.name='张正毅';--fd7a1ec7907846e382413c37e9c75e86
SELECT SYS_GUID() AS uuid FROM dual;
----将已分组丢失的数据插入到中间表
MERGE INTO cen_person_family t
USING (
select a.aaa001,a.village_group,a.fam_name,a.fam_type,a.aac191,a.is_delete,a.azc005 from (
select f.* from CEN_CHECK_FAMILY f where
f.IS_DELETE = '0' and (f.FAM_TYPE in ('01','03') or f.AAC191 in ('01','02','03'))
and f.AZC005 = '620421011001' ) a left join (
select m.family_id,m.* from cen_person_family m where person_id in (select user_id from cen_survey_person where user_id in
(select t.id from sys_user t where t.login_name like '620802022019pc004%'))
) b
on a.aaa001 = b.family_id where b.family_id is null
) t2 ON (t.family_id = t2.aaa001 ) WHEN NOT MATCHED THEN INSERT (t.id ,t.person_id, t.family_id,t.create_time, t.state,t.village_group, t.fam_name, t.azc005 ) VALUES (SYS_GUID(),'fd7a1ec7907846e382413c37e9c75e86',t2.aaa001, '2024-09-11 12:00:00', '1',t2.village_group,t2.fam_name,t2.azc005);
SELECT * FROM V$SESSION;
SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE';
SELECT * FROM V$PROCESS;
SELECT COUNT(*) FROM v$session;
SELECT name, value FROM v$parameter WHERE name = 'sessions';
select t.*,rowid from tbl_sys_dict t where t.dict_type_id='JZPCXD018BF16D4CE1547E64141BLX18';
select count(1) from sys_login_log where login_time like '2024-09-12%';
delete from sys_login_log where login_time <'2023-12-31 00:00:00';
create table sys_login_log20240912 as select * from sys_login_log t;
select
t.AREA_CODE,
t.AREA_NAME,
t.PARENT_AREA_CODE,
t.AREA_LONGITUDE,
t.AREA_LATITUDE,
t.POOR_TAG,
t.SHORT_NAME,
t.AREA_LEVEL,
t.IS_GROUP,
t.FULL_AREA_NAME
from tbl_sys_area_code t
where t.parent_area_code='620000000000' order by t.area_code asc ;
select * from cen_check_family t where t.aaa001 ='62042101300162003294969'; select * from cen_check_member t where --t.aaa001 ='86200022600460' mem_name = '马维明'
create table cen_family_azc005null as select * from cen_check_family t where t.azc005 is null;--62042100500662002677652 62042101300162002553386
select * from cen_check_member t where t.aaa001='62042101300162003294969';
select * from cen_check_oplog t where t.fm_num='ed70b4d32c9147e290b49f326126feb3';
create table cen_member_azc005null as
delete from cen_check_member t where exists (select 1 from cen_check_family f where t.aaa001=f.aaa001 and f.azc005 is null);
select count(1) from cen_check_family t where t.azc005 is null; select t.* from cen_check_family t where t.village is null; select t.*,rownum from cen_check_family t where t.azc002 is null; update cen_check_family t set t.azc002='620000000000' where t.azc001 is null; update cen_check_family t set t.azc002=substr(t.azc005,1,4)||00000000 where t.azc002 is null; update cen_check_family t set t.azc002=substr(t.azc005,1,4)||00000000 where t.azc002 is null; update cen_check_family t set t.azc003=substr(t.azc005,1,6)||000000 where t.azc003 is null; update cen_check_family t set t.azc004=substr(t.azc005,1,9)||000 where t.azc004 is null; select * from cen_area_code_bak t where t.village_code='620622019005'
MERGE INTO cen_check_family a USING ( select '甘肃省' as province,s.area_name as city,mmm.county,mmm.town,mmm.village, mmm.village_code,mmm.town_code,mmm.county_code,mmm.city_code,s.parent_area_code as province_code from ( select p.area_name as county,mm.village,mm.town, mm.village_code,mm.town_code,mm.county_code,mm.city_code from ( select n.AREA_NAME as town,m.area_name as village, m.area_code as village_code,m.parent_area_code as town_code, substr(m.PARENT_AREA_CODE,0,6)||'000000' as county_code, substr(m.PARENT_AREA_CODE,0,4)||'00000000' as city_code from TBL_SYS_AREA_CODE_TEMP m left join TBL_SYS_AREA_CODE_TEMP n on m.parent_area_code =n.area_code where m.AREA_LEVEL='5' ) mm right join TBL_SYS_AREA_CODE_TEMP p on mm.county_code = p.AREA_CODE) mmm left join TBL_SYS_AREA_CODE_TEMP s on mmm.city_code = s.area_code ) t2 ON (a.azc005 = t2.village_code) WHEN MATCHED THEN UPDATE SET a.village=t2.village WHERE a.village is null;
MERGE INTO cen_check_family a
USING (
select * from cen_area_code_bak
) t2
ON (a.azc005 =t2.village_code)
WHEN MATCHED THEN
UPDATE SET a.village = t2.village
--,a.province=t2.province
--a.city=t2.city,a.county=t2.county,a.town=t2.town,a.village=t2.village
WHERE a.village like '% %';
select t.,rowid from cen_check_family t where t.aaa001 ='86200023065260';select t.,rowid from cen_check_member t where t.aaa001 ='62042100700862002719825';
select * from cen_check_family t where t.family_address is null;
select t.,rowid from cen_check_family t where t.card_id in ('62220119850812845352');622322194011083412--t.fam_name='李彦秀'--622701198008104276select t.,rowid from cen_check_member t where t.card_id ='620521201409075274';
select t.,rowid from cen_check_family_history t where t.card_id ='622322194011083412';select t.,rowid from cen_check_member_history t where t.card_id ='62282719550116351543';
select t.azc002,t.status , count(t.status),count(t.status)/5179268 from cen_check_family t where t.status ='1' and t.is_delete='0' group by t.azc002,t.status
select t.,rowid from cen_check_family t where t.azc005 ='620823007004' and is_delete ='0' and fam_type ='02' and aac191 is null;sselect t.,rowid from cen_check_family t where t.fam_name ='张永军'
where t.card_id ='620521198209035273'; 620521201409075274
select t.*,rowid from cen_check_member t where card_id ='622301197906168371';6e43199f166d434f9d61be3d3a1abb3a
201e284b2d84489ba009d904cd21b2e8
select t.*,rowid from cen_person_family t where t.family_id ='86200022264611';
select * from sys_user where id = 'ef14e249ae894605924bdf2d2aa3ebdf'
update cen_check_family t set t.family_address= t.province||t.city||t.county||t.town||t.village
where t.family_address is null ; like '%null%';
create table cen_area_code_bak as select '甘肃省' as province,s.area_name as city,mmm.county,mmm.town,mmm.village,
mmm.village_code,mmm.town_code,mmm.county_code,mmm.city_code,s.parent_area_code as province_code from (
select p.area_name as county,mm.village,mm.town,
mm.village_code,mm.town_code,mm.county_code,mm.city_code from (
select n.AREA_NAME as town,m.area_name as village,
m.area_code as village_code,m.parent_area_code as town_code,
substr(m.PARENT_AREA_CODE,0,6)||'000000' as county_code,
substr(m.PARENT_AREA_CODE,0,4)||'00000000' as city_code
from TBL_SYS_AREA_CODE_TEMP m left join TBL_SYS_AREA_CODE_TEMP n
on m.parent_area_code =n.area_code where m.AREA_LEVEL='5'
) mm right join TBL_SYS_AREA_CODE_TEMP p
on mm.county_code = p.AREA_CODE) mmm left join TBL_SYS_AREA_CODE_TEMP s on mmm.city_code = s.area_code;
update cen_check_family t set t.aac191 ='01' where t.fam_type='02' and t.aac191 is null;
select t.*,rowid from basic_family_apply t where t.card_id ='622926198807013571' and t.year ='2024';
select t.*,t.rowid from basic_family_apply_rollback t where t.pid='180f6815f43b455d8f2a1965b3315f37';
merge into cen_check_family t using ( select f.aaa001,nvl(count(1),1) as count_num from cen_check_member f
where f.is_delete = '0'
group by aaa001) a
on (t.aaa001 = a.aaa001 and t.is_delete ='0')
when matched then update set t.family_size = a.count_num;
select count(1) from cen_check_family t where not exists (select 1 from cen_check_member f on t.aaa001 = f.aaa001);
select t.* from cen_check_family t where t.is_delete='0' and not exists (select 1 from cen_check_member f where t.aaa001=f.aaa001);
select count(1) from cen_check_family t
where t.is_delete='0'
and t.status ='1'
and not exists (select 1 from cen_person_family f where t.aaa001=f.family_id);
select t.* from cen_check_family t
where t.is_delete='0'
and t.status ='1'
and not exists (select 1 from cen_person_family f where t.aaa001=f.family_id);
select * from cen_person_family where family_id='62102700700162000317598';
--86200022392734
MERGE INTO cen_check_member t
USING (
select f.* from cen_check_family f where f.card_id = '622301197906168371'
) t2
ON (t.aaa001 = t2.aaa001 )
WHEN NOT MATCHED THEN
INSERT (t.mem_number ,t.aaa001, t.mem_name,t.mem_sex,t.card_id,
t.is_delete,t.status, t.create_datetime,t.relation_with_head)
VALUES (SYS_GUID(),t2.aaa001,t2.fam_name,t2.fam_sex,t2.card_id,'0','0' ,'2024-09-24 12:00:00','01');
INSERT INTO cen_check_member (mem_number, aaa001, mem_name, mem_sex, card_id, is_delete, status, create_datetime, relation_with_head)
SELECT SYS_GUID(), t2.aaa001, t2.fam_name, t2.fam_sex, t2.card_id, '0', '0', '2024-09-24 12:00:00', '01'
FROM cen_check_family t2
WHERE t2.card_id = '622301197906168371';
select count(1) from cen_check_member t where t.is_delete='8';
update cen_check_member set is_delete ='0' where is_delete='8';
select t.,rowid from sys_user t where login_name ='jzfp621022009007';select t. ,rowid from tbl_sys_client_info t ;
update cen_check_family t set t.cow_quantity='',t.sheep_quantity='',t.cow_quantity_out='',t.sheep_quantity_out='',
t.cow_sheep_breeding_income='' where t.fam_type='03';
--t.cow_quantity 肉牛存栏数量,t.sheep_quantity 羊存栏数量,t.cow_quantity_out 肉牛年度出栏数量,t.sheep_quantity_out 羊年度出栏数量,
--t.cow_sheep_breeding_income 牛羊养殖净收入@Here With You 这几个字段
SELECT
tt.azc005,
tt.check_type,
tt.check_type_count,
aa.azc005_all ,round(tt.check_type_count * 100.0 / aa.azc005_all, 2) AS percentage
FROM (
SELECT
azc005,
check_type,
COUNT(check_type) AS check_type_count
FROM
cen_check_family
WHERE
status = '1' AND is_delete = '0'
GROUP BY
azc005, check_type
) tt
JOIN (
SELECT
azc005,
COUNT(1) AS azc005_all
FROM
cen_check_family
WHERE
status = '1' AND is_delete = '0'
GROUP BY
azc005
) aa ON tt.azc005 = aa.azc005
WHERE
(tt.check_type = '0' AND tt.check_type_count * 100.0 / aa.azc005_all >= 80)
OR (tt.check_type = '1' AND tt.check_type_count * 100.0 / aa.azc005_all >= 80) ;
select s.aaa001,s.relation_with_head from cen_check_member s where s.relation_with_head in ('02','11','12','13','14','17','18','19','20') group by s.AAA001, s.relation_with_head having count(1)>1; 62060201700562003199235 select t.,t.rowid from cen_check_family t where card_id = '622427196608121457' ; select t.,t.rowid from cen_person_family t where t.family_id='50dcfd6fff344d1692653866ea294be9'; select m.* from cen_check_member m where m.status ='1' and exists(select 1 from ( select s.aaa001,s.relation_with_head from cen_check_member s where s.relation_with_head in ('02','11','12','13','14','17','18','19','20') group by s.AAA001, s.relation_with_head having count(1)>4) f where m.aaa001 =f .aaa001) select m.* from (select s.aaa001,s.relation_with_head from cen_check_member s where s.relation_with_head in ('02','11','12','13','14','17','18','19','20') group by s.AAA001, s.relation_with_head having count(1)>4) f, cen_check_member m where f.aaa001 =m.aaa001 ; select a.azc005,a.SINGLE_COUNT,b.ALL_COUNT,round( a.SINGLE_COUNT * 100.0 / b.ALL_COUNT,2) as percent_num from (select t.azc005,count(1) as single_count from CEN_CHECK_FAMILY t, (select m.AAA001 from CEN_CHECK_MEMBER m group by m.AAA001 having count(1) =1) mm where t.aaa001=mm.aaa001 and t.status = '1' AND t.is_delete = '0' group by t.azc005) a, (select t.azc005,count(1) as all_count from CEN_CHECK_FAMILY t
where t.status = '1' AND t.is_delete = '0'
group by t.azc005) b where a.azc005=b.azc005 and a.SINGLE_COUNT * 100.0 / b.ALL_COUNT >50;
insert into /*+ PARALLEL(CEN_WTSJ, 4) */ CEN_WTSJ
select '8' as id,'有整村80%农户排查方式为“线上问询”或“村级研判”' 问题类型,
a.province 省, a.city 市, a.county 县, a.town 乡, a.village 村,
a.azc005 行政区划代码,
CASE
WHEN a.fam_type = '01' THEN '脱贫户'
WHEN a.aac191 = '01' THEN '脱贫不稳定户'
WHEN a.aac191 = '02' THEN '边缘易致贫户'
WHEN a.aac191 = '03' THEN '突发严重困难户'
WHEN a.fam_type = '03' THEN '一般农户'
ELSE ''
END AS 户类型,
a.fam_name 户主姓名, a.card_id as 户主证件号码, b.mem_name as 成员姓名, b.card_id as 成员证件号
from cen_check_family a left join (
select t.azc005,t.aaa001,m.mem_name,m.CARD_ID from cen_check_family t ,cen_check_member m where t.aaa001= m.aaa001 and
t.IS_DELETE='0' group by t.azc005,t.aaa001,m.mem_name,m.CARD_ID having count(1)>1 ) b
on a.aaa001 =b.aaa001 where a.is_delete='0'and b.aaa001 is not null ;
select t.azc003,t.card_id from cen_check_family t where t.IS_DELETE='0' group by t.azc003,CARD_ID having count(1)>1 ;
select t.azc003,t.card_id from cen_check_family t where t.IS_DELETE='0' group by t.azc003,CARD_ID having count(1)>1 ;
select '14' as id,'2023年度户生产经营性收入中牛羊（不含牦牛）售卖净收入小于100元' 问题类型, t.province 省, t.city 市, t.county 县, t.town 乡, t.village 村, t.azc005 行政区划代码, CASE WHEN t.fam_type = '01' THEN '脱贫户' WHEN t.aac191 = '01' THEN '脱贫不稳定户' WHEN t.aac191 = '02' THEN '边缘易致贫户' WHEN t.aac191 = '03' THEN '突发严重困难户' WHEN t.fam_type = '03' THEN '一般农户' ELSE ''
END AS 户类型,
t.fam_name 户主姓名, t.card_id as 户主证件号码, '' as 成员姓名, '' as 成员证件号
from CEN_CHECK_FAMILY t
where t.is_delete = '0' and nvl(t.cow_Sheep_Breeding_Income,0)<100 ;
