-- ① SDC만 (SBC 행만 · 로그인 시 온보딩)
SELECT USR_ID
  FROM TN_MDSS_USR
 WHERE USE_YN = 'Y' AND USR_KIND_NM IN ('SBC','MSE')
 GROUP BY USR_ID
HAVING SUM(CASE WHEN USR_KIND_NM = 'SBC' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN USR_KIND_NM = 'MSE' THEN 1 ELSE 0 END) = 0
 ORDER BY USR_ID;

-- ② MDIS만 (MSE 행만)
SELECT USR_ID
  FROM TN_MDSS_USR
 WHERE USE_YN = 'Y' AND USR_KIND_NM IN ('SBC','MSE')
 GROUP BY USR_ID
HAVING SUM(CASE WHEN USR_KIND_NM = 'MSE' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN USR_KIND_NM = 'SBC' THEN 1 ELSE 0 END) = 0
 ORDER BY USR_ID;

-- ③ dual (SBC + MSE 둘 다)
SELECT USR_ID
  FROM TN_MDSS_USR
 WHERE USE_YN = 'Y' AND USR_KIND_NM IN ('SBC','MSE')
 GROUP BY USR_ID
HAVING SUM(CASE WHEN USR_KIND_NM = 'SBC' THEN 1 ELSE 0 END) > 0
   AND SUM(CASE WHEN USR_KIND_NM = 'MSE' THEN 1 ELSE 0 END) > 0
 ORDER BY USR_ID;

SELECT MBER_DIV, COUNT(*) AS USR_CNT
  FROM (
        SELECT USR_ID,
               CASE WHEN SUM(CASE WHEN USR_KIND_NM = 'MSE' THEN 1 ELSE 0 END) = 0 THEN '1.SDC만'
                    WHEN SUM(CASE WHEN USR_KIND_NM = 'SBC' THEN 1 ELSE 0 END) = 0 THEN '2.MDIS만'
                    ELSE '3.SDC+MDIS(dual)'
               END AS MBER_DIV
          FROM TN_MDSS_USR
         WHERE USE_YN = 'Y'
           AND USR_KIND_NM IN ('SBC','MSE')
         GROUP BY USR_ID
       )
 GROUP BY MBER_DIV
 ORDER BY MBER_DIV;