SELECT * FROM tb
WHERE
DECODE(month_1,0,0,1) ||
DECODE(month_2,0,0,1) ||
DECODE(month_3,0,0,1) ||
DECODE(month_4,0,0,1) ||
DECODE(month_5,0,0,1) ||
DECODE(month_6,0,0,1) ||
DECODE(month_7,0,0,1) ||
DECODE(month_8,0,0,1) ||
DECODE(month_9,0,0,1) ||
DECODE(month_10,0,0,1) ||
DECODE(month_11,0,0,1)||
DECODE(month_12,0,0,1)
LIKE '%000000%'
SELECT * FROM tb
WHERE
DECODE(month_1,null,0,0,0,1) ||
DECODE(month_2,null,0,0,0,1) ||
DECODE(month_3,null,0,0,0,1) ||
DECODE(month_4,null,0,0,0,1) ||
DECODE(month_5,null,0,0,0,1) ||
DECODE(month_6,null,0,0,0,1) ||
DECODE(month_7,null,0,0,0,1) ||
DECODE(month_8,null,0,0,0,1) ||
DECODE(month_9,null,0,0,0,1) ||
DECODE(month_10,null,0,0,0,1)||
DECODE(month_11,null,0,0,0,1)||
DECODE(month_12,null,0,0,0,1)
LIKE '%000000%'
SELECT TO_DATE(TO_CHAR(year_index,'9999')+'-01-01','yyyy-MM-dd') ym, member_id, month_1 fee FROM table1
UNION ALL
SELECT TO_DATE(TO_CHAR(year_index,'9999')+'-02-01','yyyy-MM-dd') ym, member_id, month_2 fee FROM table1
...
UNION ALL
SELECT TO_DATE(TO_CHAR(year_index,'9999')+'-12-01','yyyy-MM-dd') ym, member_id, month_12 fee FROM table1
)
,t2 AS ( -- 排序
SELECT *,
ROW_NUMBER() OVER(PARTITION BY member_id ORDER BY ym) rn
FROM t1
WHERE fee IS NOT NULL
)
-- 求前后两次间隔超6月的
SELECT t2.member_id,
t2.ym,
t3.ym
FROM t2
JOIN t2 t3
ON t3.member_id = t2.member_id
AND t3.rn = t2.rn + 1
WHERE DateDiff(m,t2.ym,t3.ym) > 6
如果只需要列出人员,对结果再做 DISTINCT。
没有入党信息,所以开始没缴费的不能统计。
SELECT MEMBER_ID, REPLACE(WM_CONCAT(S_MON), ',', '')
FROM (SELECT MEMBER_ID,
DECODE(MONTH_1, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_2, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_3, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_4, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_5, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_6, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_7, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_8, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_9, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_10, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_11, NULL, 0, 0, 0, 1) ||
DECODE(MONTH_12, NULL, 0, 0, 0, 1) S_MON
FROM TMPSA
ORDER BY YEAR_INDEX)
GROUP BY MEMBER_ID
HAVING REPLACE(WM_CONCAT(S_MON), ',', '') LIKE '%000000%'
t2 AS ( -- 排序
SELECT T1.*,
ROW_NUMBER() OVER(PARTITION BY member_id ORDER BY ym) rn
FROM t1
WHERE fee IS NOT NULL
)