Mysql 쿼리를 연습하다
남들이 해놓은거 많이 봐서 잘 할줄 알았는데 아니었다. 역시... 백문이불여일타!!!!!
서브쿼리 지양하기!
처음에는 이런식으로 쿼리를 짬
SELECT
TRMNL_ID, MNFC_NM, MDL_NM, MNFC_SRL_NO
, GTWY_ID
, ( SELECT tb.USIM_NO
FROM M_GTWY_INF AS ta
LEFT JOIN M_USIM_INF AS tb
ON ta.USIM_ID = tb.USIM_ID
WHERE ta.GTWY_ID = a.GTWY_ID
) AS USIM_NO -- 유심번호
, TRMNL_NM
, (
SELECT SCN_NM FROM m_scn_inf WHERE SCN_ID = (
SELECT td.SCN_ID
FROM m_dust_trmnl_inf AS tc
LEFT JOIN m_scn_dust_trmnl_reg_inf AS td
ON tc.TRMNL_ID = td.TRMNL_ID
WHERE tc.TRMNL_ID = a.TRMNL_ID )
) AS SCN_NM
, RMRK
, (
SELECT te.CD_NM FROM m_cd_inf AS te
WHERE te.CD_GRP_ID = 'STS_CD'
AND te.CD_VAL = a.STS_CD
) AS STS_CD -- 상태코드
, USE_YN, REG_DT
FROM m_dust_trmnl_inf AS a
ORDER BY TRMNL_ID ASC ;
-- 혼자 피드백 : 정답 쿼리를 확인해보니
-- 서브쿼리로 사용한 SELECT WHERE 보다도
-- 메인쿼리에서 LEFT JOIN 을 하는 방향이
-- 좋을 듯함.
그리고 피드백 받고나서 서브쿼리 최대한 안함
-- 디바이스관리 -- :: 안보고 작성한 것
-- 게이트웨이ID / 펌웨어버전 / 제조사명 / 모델명 / 제조사SN / 전압 / 유심번호 / 유심사용여부 / 상태코드 / 비고 / 원시데이터저장여부 / 사용여부 / 입고일시(사용 시작 일자)
-- GTWY_ID, FW_VER, MNFC_NM, MDL_NM, GTWY_NO, CD_GTWY_CLS, 유심번호, 유심사용여부, STS_CD,RMRK,RAW_SAVE_YN,USE_YN, USE_STRT_YMD
SELECT
a.GTWY_ID
, a.FW_VER
, a.MNFC_NM
, a.MDL_NM
, a.GTWY_NO
, c.CD_NM
, b.USIM_NO
, b.USE_YN AS USIM_USE_YN
, d.CD_NM
, a.RMRK
, a.RAW_SAVE_YN
, a.USE_YN
, a.USE_STRT_YMD
FROM jbq.M_GTWY_INF AS a
LEFT JOIN jbq.M_USIM_INF AS b
ON a.USIM_ID = b.USIM_ID
LEFT JOIN jbq.M_CD_INF AS c
ON a.GTWY_CLS = c.CD_VAL
AND c.CD_GRP_ID = 'GTWY_CLS'
LEFT JOIN jbq.M_CD_INF AS d
ON a.STS_CD = d.CD_VAL
AND d.CD_GRP_ID = 'STS_CD';
--
-- 혼자 피드백 : 정답 쿼리를 확인해보니
-- 오늘은 어제보다 서브쿼리를 쓰지 않으려고 노력했음.
-- 새로 알아낸 것 : LEFT JOIN 후 키를
매칭 시킬 때, 처음에는 ON을 2번 써서 에러남.
ON 다음에는 AND로 연결하기.
-- --------------------------------------------------------------------------------------------------------------
-- 정답 찾아가는 중 ::::: 전압 GTWY_CLS / 1 : 12V, 2 : 5V ::::: 상태코드 / 공단말기-정상, 개통단말기-정상 등등
-- --------------------------------------------------------------------------------------------------------------
-- STEP 1
SELECT CD_VAL, CD_NM FROM jbq.M_CD_INF WHERE CD_GRP_ID = 'GTWY_CLS';
-- STEP 2
SELECT c.CD_NM
FROM jbq.M_GTWY_INF AS a
LEFT JOIN jbq.M_CD_INF AS c
ON a.GTWY_CLS = c.CD_VAL
WHERE c.CD_GRP_ID = 'GTWY_CLS';
-- STEP 3
SELECT d.CD_NM
FROM jbq.M_GTWY_INF AS a
LEFT JOIN jbq.M_CD_INF AS d
ON a.STS_CD = d.CD_VAL
WHERE d.CD_GRP_ID = 'STS_CD';
-- --------------------------------------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------------------------------------
-- 아래는 정답 쿼리
-- --------------------------------------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------------------------------------
SELECT a.GTWY_ID,
a.FW_VER,
a.MNFC_NM,
a.MDL_NM,
a.GTWY_NO,
b.CD_NM AS CD_GTWY_CLS,
CASE LENGTH(c.USIM_NO)
WHEN 11 THEN CONCAT(LEFT(c.USIM_NO, 3), '-', MID(c.USIM_NO, 4, 4), '-', RIGHT(c.USIM_NO, 4))
WHEN 10 THEN CONCAT(LEFT(c.USIM_NO, 3), '-', MID(c.USIM_NO, 4, 3), '-', RIGHT(c.USIM_NO, 4))
END AS USIM_NO,
c.USE_YN AS USIM_USE_YN,
d.CD_NM AS CD_STS_CD,
a.GTWY_CLS,
a.RMRK,
a.RAW_SAVE_YN,
a.USE_YN,
a.USE_STRT_YMD
FROM
m_gtwy_inf AS a
LEFT JOIN
m_cd_inf AS b
ON
a.GTWY_CLS = b.CD_VAL AND
b.CD_GRP_ID = 'GTWY_CLS'
LEFT JOIN
m_usim_inf AS c
ON
a.USIM_ID = c.USIM_ID
LEFT JOIN
m_cd_inf AS d
ON
a.STS_CD = d.CD_VAL AND
d.CD_GRP_ID = 'STS_CD';
728x90
300x250
