oracle表连接、联合更新-526互联


-- 插入反馈
-- 插入反馈
-- 插入反馈
INSERT INTO WBJH_GA_FKXX
(
SELECT 
lower(RAWTOHEX(sys_guid())) AS ID,
eb.YCT_APP_NO,
eb.UNISCID,
'3' AS ZT,
dd.seal_time AS ZTFSSJ,
dd.seal_time AS SJTB_SJ,
'0' AS FLAG,
dd.seal_time AS SJHQSJ
-- eb.*,dd.seal_time,eb.S_EXT_TIMESTAMP
FROM "0339_wfk" w
LEFT JOIN YCT_GONGA_E_BASEINFO eb ON eb.UNISCID=w."统一社会信用代码"
LEFT JOIN WBJH_GA_FKXX f ON f.UNISCID=w."统一社会信用代码"
LEFT JOIN 
(
-- 获取订单提交时间
SELECT USER_ID,COUNT(*) AS total,
-- 字符串转DATE
TO_DATE(listagg ( TO_CHAR(S_EXT_TIMESTAMP,'yyyy-mm-dd HH24:mi:ss'), ',' ) within GROUP ( ORDER BY S_EXT_TIMESTAMP ),'yyyy-mm-dd HH24:mi:ss') AS seal_time 
FROM YCT_GONGA_YZCJXX 
WHERE USER_ID IN(SELECT "统一社会信用代码" FROM "0339_wfk") GROUP BY USER_ID HAVING COUNT(*)>0
) dd ON dd.USER_ID=w."统一社会信用代码"
WHERE f.ZT IS NULL AND eb.UNISCID IS NOT NULL AND dd.USER_ID IS NOT NULL
)

-- 更新反馈
-- 更新反馈
-- 更新反馈（ORCALE联合更新）
UPDATE WBJH_GA_FKXX gafk SET (UUID,YCT_APP_NO,UNISCID,ZT,ZTFSSJ,SJTB_SJ,FLAG,SJHQSJ)=
(
SELECT * FROM 
  -- 关联表，这里是子查询AS tt 表   
	(
	SELECT 
	lower(RAWTOHEX(sys_guid())) AS ID,
	eb.YCT_APP_NO,
	eb.UNISCID,
	'3' AS ZT,
	dd.seal_time AS ZTFSSJ,
	dd.seal_time AS SJTB_SJ,
	'0' AS FLAG,
	dd.seal_time AS SJHQSJ
	-- eb.*,dd.seal_time,eb.S_EXT_TIMESTAMP
	FROM "0339_wfk" w
	LEFT JOIN YCT_GONGA_E_BASEINFO eb ON eb.UNISCID=w."统一社会信用代码"
	LEFT JOIN WBJH_GA_FKXX f ON f.UNISCID=w."统一社会信用代码"
	LEFT JOIN 
	(
	-- 获取订单提交时间
	SELECT USER_ID,COUNT(*) AS total,
	-- 字符串转DATE
	TO_DATE(listagg ( TO_CHAR(S_EXT_TIMESTAMP,'yyyy-mm-dd HH24:mi:ss'), ',' ) within GROUP ( ORDER BY S_EXT_TIMESTAMP ),'yyyy-mm-dd HH24:mi:ss') AS seal_time 
	FROM YCT_GONGA_YZCJXX 
	WHERE USER_ID IN(SELECT "统一社会信用代码" FROM "0339_wfk") GROUP BY USER_ID HAVING COUNT(*)>0
	) dd ON dd.USER_ID=w."统一社会信用代码"
	) tt
-- WBJH_GA_FKXX与关联表的连接条件，不然会有多列无法更新错误
WHERE tt.UNISCID=gafk.UNISCID
)
-- WBJH_GA_FKXX再次限定条件
WHERE gafk.UNISCID IN (SELECT "统一社会信用代码" FROM "0339_wfk");





-- 
SELECT COUNT(*) FROM WBJH_GA_FKXX WHERE UNISCID IN (SELECT "统一社会信用代码" FROM "0339_wfk");