相关数据已经脱敏处理
原SQL
MERGE INTO IntoTable t USING (
SELECT
x.PID,
b.SEC_ID,
b.T_SYB,
b.E_CD,
x.END_DATE,
x.xh 区间级别,
isnull(x.hj, 0) 区间人数
FROM
(
SELECT
a.PID,
a.END_DATE,
a.xh,
c.hj
FROM
(
SELECT
PID,
END_DATE,
row_number () OVER (
PARTITION BY PID,
END_DATE
ORDER BY
END_DATE
) xh
FROM
ScTable a
WHERE
EXISTS (
SELECT
*
FROM
(
SELECT
PID,
END_DATE,
COUNT(*) hj
FROM
ScTable
GROUP BY
PID,
END_DATE
HAVING
COUNT(*) >= 5
) b
WHERE
a.PID = b.PID
AND a.END_DATE = b.END_DATE
)
AND EXISTS (
SELECT
*
FROM
ScTable c
WHERE
UPDATE_TIME >= CONVERT (VARCHAR(20), GETDATE() - 1, 23)
AND R_TYPE = '5'
AND a.PID = c.PID
AND a.END_DATE = c.END_DATE
)
AND a.DR = '1'
) a
LEFT JOIN (
SELECT
PID,
END_DATE,
jb,
COUNT(*) hj
FROM
(
SELECT
PID,
END_DATE,
mm,
CASE
WHEN mm >= 0
AND mm <= 200000 THEN
5
WHEN mm > 200000
AND mm <= 500000 THEN
4
WHEN mm > 500000
AND mm <= 1000000 THEN
3
WHEN mm > 1000000
AND mm <= 2000000 THEN
2
WHEN mm > 2000000 THEN
1
END jb
FROM
(
SELECT
PID,
END_DATE,
CASE
WHEN A_REWARD IS NULL THEN
0