ShangHaiLifeECIF/sql-achievement/存储过程/人寿测试环境使用sql整理/相似客户.sql

CREATE OR REPLACE PROCEDURE shanglifeecif.same_customer() -- 创建主存储过程
IS 
DECLARE
	scount int
BEGIN
DELETE FROM shanglifeecif.samecustomer;

INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType
) 
SELECT 
	row_number()OVER(),
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	0
FROM shanglifeecif.customertotaltemp sc1 
WHERE sc1.mobile IS NOT NULL AND EXISTS (SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc1.scustid != sc2.scustid 
		AND sc1.name = sc2.name AND sc1.gender = sc2.gender AND sc1.idtype = sc2.idtype AND sc1.mobile = sc2.mobile);
		
	SELECT count(*) INTO scount FROM shanglifeecif.samecustomer;
	--三要素疑似相同指姓名、性别、出生日期一致   1
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType

) 
SELECT 
	row_number()OVER()+scount,
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	1
FROM shanglifeecif.customertotaltemp sc1 
WHERE EXISTS(SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc2.scustid!=sc1.scustid AND sc1.name = sc2.name AND sc1.gender = sc2.gender AND sc1.birthday = sc2.birthday );

	SELECT count(*) INTO scount FROM shanglifeecif.samecustomer;
	--两要素疑似相同指姓名、证件号码一致 2
	INSERT INTO shanglifeecif.samecustomer(
  		sid,
  		scustid,
  		name,
  		gender,
  		birthday,
  		idcard,
  		Passport,
  		Dlicense,
  		OtherIdNumber,
  		sameType

) 
SELECT 
	row_number()OVER()+scount,
	sc1.scustid,
	sc1.name,
	sc1.gender,
	sc1.birthday,
	CASE sc1.idtype WHEN '0' THEN sc1.idcard END AS idcard,
	CASE sc1.idtype WHEN '1' THEN sc1.idcard END AS passport,
	CASE sc1.idtype WHEN '3' THEN sc1.idcard END AS dlicense,
	CASE  WHEN sc1.idtype <> '0' AND sc1.idtype <> '1' AND sc1.idtype <> '3' THEN sc1.idcard END AS otherIdnumber,
	2
FROM shanglifeecif.customertotaltemp sc1 

WHERE sc1.idcard is not null and  EXISTS (SELECT 1 FROM shanglifeecif.customertotaltemp sc2 WHERE sc2.scustid!=sc1.scustid AND sc1.name = sc2.name AND sc1.idcard = sc2.idcard);

	
END;