INSERT INTO AC_GROUP_OPER
(AGO_ID, AGO_MSISDN, AGO_IMSI, AGO_IMEI, GI_GROUP_ID)
SELECT DISTINCT agb.AGB_ID,
agb.AGB_MSISDN,
substr(agb.AGB_IMSI, 1, 5),
MAX(substr(agb.AGB_IMEI, 1, 8)),
sgi.GI_GROUP_ID
FROM AC_GROUP_BUFF agb
INNER JOIN (SELECT AGB_MSISDN, MAX(AGB_CRE_TIME) TTIME
FROM AC_GROUP_BUFF
GROUP BY AGB_MSISDN) tt ON agb.AGB_MSISDN = tt.AGB_MSISDN
AND agb.AGB_CRE_TIME = tt.TTIME
INNER JOIN SYS_GROUP_IMSI sgi ON sgi.GI_IMSI =
substr(agb.AGB_IMSI, 1, 5)
WHERE ROWNUM <= 2500
GROUP BY agb.AGB_ID,
agb.AGB_MSISDN,
substr(agb.AGB_IMSI, 1, 5),
sgi.GI_GROUP_ID
去掉AGB_MSISDN 相同的数据并取 相同记录中AGB_CRE_TIME中较大的一个 这样的语句可以吗
当AGB_MSISDN, MAX(AGB_CRE_TIME) 组合相同时 不能去掉
怎么才能去掉
(AGO_ID, AGO_MSISDN, AGO_IMSI, AGO_IMEI, GI_GROUP_ID)
SELECT DISTINCT agb.AGB_ID,
agb.AGB_MSISDN,
substr(agb.AGB_IMSI, 1, 5),
MAX(substr(agb.AGB_IMEI, 1, 8)),
sgi.GI_GROUP_ID
FROM AC_GROUP_BUFF agb
INNER JOIN (SELECT AGB_MSISDN, MAX(AGB_CRE_TIME) TTIME
FROM AC_GROUP_BUFF
GROUP BY AGB_MSISDN) tt ON agb.AGB_MSISDN = tt.AGB_MSISDN
AND agb.AGB_CRE_TIME = tt.TTIME
INNER JOIN SYS_GROUP_IMSI sgi ON sgi.GI_IMSI =
substr(agb.AGB_IMSI, 1, 5)
WHERE ROWNUM <= 2500
GROUP BY agb.AGB_ID,
agb.AGB_MSISDN,
substr(agb.AGB_IMSI, 1, 5),
sgi.GI_GROUP_ID
去掉AGB_MSISDN 相同的数据并取 相同记录中AGB_CRE_TIME中较大的一个 这样的语句可以吗
当AGB_MSISDN, MAX(AGB_CRE_TIME) 组合相同时 不能去掉
怎么才能去掉
INSERT INTO AC_GROUP_OPER
(AGO_ID, AGO_MSISDN, AGO_IMSI, AGO_IMEI, GI_GROUP_ID)
SELECT DISTINCT agb.AGB_ID,
agb.AGB_MSISDN,
substr(agb.AGB_IMSI, 1, 5),
MAX(substr(agb.AGB_IMEI, 1, 8)),
sgi.GI_GROUP_ID
FROM AC_GROUP_BUFF,
(SELECT t.*, row_number() over(parition BY AGB_MSISDN ORDER BY AGB_CRE_TIME DESC) rn
FROM agb t) agb
INNER JOIN SYS_GROUP_IMSI sgi
ON sgi.GI_IMSI = substr(agb.AGB_IMSI, 1, 5)
WHERE ROWNUM <= 2500 AND
agb.rn = 1
GROUP BY agb.AGB_ID, agb.AGB_MSISDN, substr(agb.AGB_IMSI, 1, 5), sgi.GI_GROUP_ID