会python真的可以为所欲为0(回)
352天前
这里还有人吗1(回)
538天前这里还有人吗0(回)
538天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
762天前到处都是羊，不想上班0(回)
842天前
鸽子
0(回)
846天前DROP TABLE IT_HEJY.T_CUST_RECLAIM_TEMP; |
CREATE TABLE IT_HEJY.T_CUST_RECLAIM_TEMP |
(CUST_ID NUMBER, |
STATE NUMBER, |
CUSTOM_TYPE_ID NUMBER, |
OLD_AREA_ID NUMBER, |
OLD_AREA_NAME VARCHAR2(128), |
OLD_AREA_CNT NUMBER DEFAULT 0, |
OLD_AREA_CNT_ZW NUMBER DEFAULT 0, |
OLD_AREA_CNT_C NUMBER DEFAULT 0, |
OLD_AREA_CNT_G NUMBER DEFAULT 0, |
OLD_AREA_CNT_QT NUMBER DEFAULT 0, |
NEW_AREA_ID NUMBER DEFAULT 0, |
NEW_AREA_NAME VARCHAR2(128), |
NEW_AREA_CNT NUMBER DEFAULT 0, |
NEW_AREA_CNT_ZW NUMBER DEFAULT 0, |
NEW_AREA_CNT_C NUMBER DEFAULT 0, |
NEW_AREA_CNT_G NUMBER DEFAULT 0, |
NEW_AREA_CNT_QT NUMBER DEFAULT 0, |
SERV_OWE NUMBER DEFAULT 0, |
ACCT_OWE NUMBER DEFAULT 0 |
); |
DELETE FROM IT_HEJY.T_CUST_RECLAIM_TEMP; |
INSERT INTO IT_HEJY.T_CUST_RECLAIM_TEMP(CUST_ID,STATE,CUSTOM_TYPE_ID,OLD_AREA_ID,OLD_AREA_NAME,OLD_AREA_CNT,OLD_AREA_CNT_ZW, |
OLD_AREA_CNT_C,OLD_AREA_CNT_G,OLD_AREA_CNT_QT) |
SELECT A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME, |
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_AREA_ID THEN B.SERV_ID ELSE NULL END) CNT, |
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_ZW, |
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.PROD_TYPE='CDMA' AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_C, |
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.PROD_TYPE IN ('GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_G, |
COUNT(CASE WHEN A.LOCAL_AREA_ID=B.LOCAL_aREA_ID AND B.PROD_TYPE NOT IN ('CDMA','GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_QT |
FROM SXDM.SXBD_CUST_AREA_LOCK_201504 A |
INNER JOIN IT_HEJY.SXBD_SERV_MSG_MON_201504 B |
ON A.CUST_ID=B.CUST_ID |
INNER JOIN SXDM.SXMART_AREA_C5_SL C |
ON A.LOCAL_AREA_ID=C.AREA_ID |
WHERE A.LOCAL_AREA_ID IN (33292, 33284, 33293, 33281, 33283, 33285, 33294) |
AND A.STATE IN (1, 2, -9) |
GROUP BY A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME; |
|
MERGE INTO IT_HEJY.T_CUST_RECLAIM_TEMP A |
USING ( |
SELECT A.*,ROW_NUMBER() OVER(PARTITION BY A.CUST_ID ORDER BY C.LVL DESC,A.CNT_ZW DESC,A.CNT_G DESC,A.CNT_C DESC,A.CNT DESC) RNUM |
FROM ( |
SELECT A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME,COUNT(B.SERV_ID) CNT,COUNT(DECODE(B.STATE,'F1X',NULL,B.SERV_ID)) CNT_ZW, |
COUNT(CASE WHEN B.PROD_TYPE='CDMA' AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_C, |
COUNT(CASE WHEN B.PROD_TYPE IN ('GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_G, |
COUNT(CASE WHEN B.PROD_TYPE NOT IN ('CDMA','GH','KD','ITV') AND B.STATE<>'F1X' THEN B.SERV_ID ELSE NULL END) CNT_QT |
FROM SXDM.SXBD_CUST_AREA_LOCK_201504 A |
INNER JOIN IT_HEJY.SXBD_SERV_MSG_MON_201504 B |
ON A.CUST_ID=B.CUST_ID |
INNER JOIN SXDM.SXMART_AREA_C5_SL C |
ON B.LOCAL_AREA_ID=C.AREA_ID |
INNER JOIN SXDM.SXMART_AREA_C5_SL D |
ON A.LOCAL_aREA_ID=D.AREA_ID |
WHERE A.LOCAL_AREA_ID IN (33292, 33284, 33293, 33281, 33283, 33285, 33294) |
AND A.STATE IN (1, 2, -9) |
AND C.AREA_ID<>D.AREA_ID |
AND C.C5_AREA_ID=D.C5_AREA_ID |
GROUP BY A.CUST_ID,A.STATE,A.CUSTOM_TYPE_ID,C.AREA_ID,C.AREA_NAME) A) B |
ON (A.CUST_ID=B.CUST_ID AND B.RNUM=1) |
WHEN MATCHED THEN |
UPDATE SET A.NEW_AREA_ID=B.AREA_ID,A.NEW_AREA_NAME=B.AREA_NAME,A.NEW_AREA_CNT=B.CNT,A.NEW_aREA_CNT_ZW=B.CNT_ZW, |
A.NEW_AREA_CNT_C=B.CNT_C,A.NEW_AREA_CNT_G=B.CNT_G,A.NEW_AREA_CNT_QT=B.CNT_QT; |
