데이터베이스 질문과 답변
SELECT
a.value AS aValue,
b.value AS bValue,
c.value AS cValue,
d.value AS dValue
FROM
(SELECT A.value,
FROM valueTable A
WHERE A.PK1 = :PK1
AND A.PK2 = :PK2
AND A.PK3 = :PK3
AND A.PK4 = :PK4
AND A.PK5 = '10'
ORDER BY A.REG_DATE DESC
LIMIT 1
)a
,
(SELECT A.value,
FROM valueTable A
WHERE A.PK1 = :PK1
AND A.PK2 = :PK2
AND A.PK3 = :PK3
AND A.PK4 = :PK4
AND A.PK5 = '20'
ORDER BY A.REG_DATE DESC
LIMIT 1
)b
,
(SELECT A.value,
FROM valueTable A
WHERE A.PK1 = :PK1
AND A.PK2 = :PK2
AND A.PK5 = '30'
ORDER BY A.REG_DATE DESC
LIMIT 1
)c
,
(SELECT A.value,
FROM valueTable A
WHERE A.PK1 = :PK1
AND A.PK2 = :PK2
AND A.PK5 = '40'
ORDER BY A.REG_DATE DESC
LIMIT 1
)d
처음엔 조건절에 in절로 max값 조건을 걸었었는데
쿼리가 느린거 같아서..여쭤봅니다..
한행에 갖고와야해서 인라인뷰를 썼는데
인라인뷰가 문제일까요?
by 마농
[2024.07.16 16:54:37]
SELECT MIN(CASE pk5 WHEN '10' THEN value END) v10
, MIN(CASE pk5 WHEN '20' THEN value END) v20
, MIN(CASE pk5 WHEN '30' THEN value END) v30
, MIN(CASE pk5 WHEN '40' THEN value END) v40
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY pk5 ORDER BY reg_date DESC) rn
, pk5
, value
FROM valueTable
WHERE pk1 = :pk1
AND pk2 = :pk2
AND pk3 = :pk3
AND pk4 = :pk4
AND pk5 IN ('10', '20', '30', '40')
) a
WHERE rn = 1
;
by mbj1
[2024.07.16 17:36:13]
답변 감사드립니다 정확히 이해되었습니다
사실 이게 원본쿼리인데 다른테이블을 조인해서 값을 가져와야되는데
이런경우에는 어떻게 응용해야 할까요 ㅠㅠ
쿼리는 길어서 a테이블만 올렸습니다!
SELECT a.value
a.cdVal01
a.cdVal02
a.cdVal03
FROM (
SELECT A.value
B.cdVal01,
B.cdVal02,
B.cdVal03
FROM valueTable A
LEFT JOIN codeTable B
ON B.CODE = 'PK5'
AND B.DTL_CD = A.PK5
WHERE A.PK1 = :PK1
AND A.PK2 = :PK2
AND A.PK3 = :PK3
AND A.PK4 = :PK4
AND A.PK5 = '10'
ORDER BY A.REG_DATE DESC LIMIT 1
) a
by 마농
[2024.07.16 17:58:53]
SELECT MIN(CASE pk5 WHEN '10' THEN value END) value_10
, MIN(CASE pk5 WHEN '10' THEN cdVal01 END) cdVal01_10
, MIN(CASE pk5 WHEN '10' THEN cdVal02 END) cdVal02_10
, MIN(CASE pk5 WHEN '10' THEN cdVal03 END) cdVal03_10
, MIN(CASE pk5 WHEN '20' THEN value END) value_20
, MIN(CASE pk5 WHEN '20' THEN cdVal01 END) cdVal01_20
, MIN(CASE pk5 WHEN '20' THEN cdVal02 END) cdVal02_20
, MIN(CASE pk5 WHEN '20' THEN cdVal03 END) cdVal03_20
, MIN(CASE pk5 WHEN '30' THEN value END) value_30
, MIN(CASE pk5 WHEN '30' THEN cdVal01 END) cdVal01_30
, MIN(CASE pk5 WHEN '30' THEN cdVal02 END) cdVal02_30
, MIN(CASE pk5 WHEN '30' THEN cdVal03 END) cdVal03_30
, MIN(CASE pk5 WHEN '40' THEN value END) value_40
, MIN(CASE pk5 WHEN '40' THEN cdVal01 END) cdVal01_40
, MIN(CASE pk5 WHEN '40' THEN cdVal02 END) cdVal02_40
, MIN(CASE pk5 WHEN '40' THEN cdVal03 END) cdVal03_40
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY a.pk5 ORDER BY a.reg_date DESC) rn
, a.pk5
, a.value
, b.cdVal01
, b.cdVal02
, b.cdVal03
FROM valueTable a
LEFT JOIN codeTable b
ON b.dtl_cd = a.pk5
AND b.code = 'PK5'
WHERE a.pk1 = :pk1
AND a.pk2 = :pk2
AND a.pk3 = :pk3
AND a.pk4 = :pk4
AND a.pk5 IN ('10', '20', '30', '40')
) a
WHERE rn = 1
;
by mbj1
[2024.07.16 17:59:47]
감사합니다!!!!
그냥다 MIN(그룹함수)걸어주면 되는거였네요..ㅎㅎ
by 마농
[2024.07.17 08:29:58]
SELECT MIN(CASE a.pk5 WHEN '10' THEN a.value END) value_10
, MIN(CASE a.pk5 WHEN '10' THEN b.cdVal01 END) cdVal01_10
, MIN(CASE a.pk5 WHEN '10' THEN b.cdVal02 END) cdVal02_10
, MIN(CASE a.pk5 WHEN '10' THEN b.cdVal03 END) cdVal03_10
, MIN(CASE a.pk5 WHEN '20' THEN a.value END) value_20
, MIN(CASE a.pk5 WHEN '20' THEN b.cdVal01 END) cdVal01_20
, MIN(CASE a.pk5 WHEN '20' THEN b.cdVal02 END) cdVal02_20
, MIN(CASE a.pk5 WHEN '20' THEN b.cdVal03 END) cdVal03_20
, MIN(CASE a.pk5 WHEN '30' THEN a.value END) value_30
, MIN(CASE a.pk5 WHEN '30' THEN b.cdVal01 END) cdVal01_30
, MIN(CASE a.pk5 WHEN '30' THEN b.cdVal02 END) cdVal02_30
, MIN(CASE a.pk5 WHEN '30' THEN b.cdVal03 END) cdVal03_30
, MIN(CASE a.pk5 WHEN '40' THEN a.value END) value_40
, MIN(CASE a.pk5 WHEN '40' THEN b.cdVal01 END) cdVal01_40
, MIN(CASE a.pk5 WHEN '40' THEN b.cdVal02 END) cdVal02_40
, MIN(CASE a.pk5 WHEN '40' THEN b.cdVal03 END) cdVal03_40
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY pk5 ORDER BY reg_date DESC) rn
, pk5
, value
FROM valueTable
WHERE pk1 = :pk1
AND pk2 = :pk2
AND pk3 = :pk3
AND pk4 = :pk4
AND pk5 IN ('10', '20', '30', '40')
) a
LEFT JOIN codeTable b
ON b.dtl_cd = a.pk5
AND b.code = 'PK5'
WHERE a.rn = 1
;
by mbj1
[2024.07.18 16:52:31]
pk5가 30,40일땐 pk3,4의 조건이 빠져야하는데
이럴떈 어떻게 해야할까요???
by 마농
[2024.07.19 08:41:29]
SELECT MIN(CASE a.pk5 WHEN '10' THEN a.value END) value_10
, MIN(CASE a.pk5 WHEN '10' THEN b.cdVal01 END) cdVal01_10
, MIN(CASE a.pk5 WHEN '10' THEN b.cdVal02 END) cdVal02_10
, MIN(CASE a.pk5 WHEN '10' THEN b.cdVal03 END) cdVal03_10
, MIN(CASE a.pk5 WHEN '20' THEN a.value END) value_20
, MIN(CASE a.pk5 WHEN '20' THEN b.cdVal01 END) cdVal01_20
, MIN(CASE a.pk5 WHEN '20' THEN b.cdVal02 END) cdVal02_20
, MIN(CASE a.pk5 WHEN '20' THEN b.cdVal03 END) cdVal03_20
, MIN(CASE a.pk5 WHEN '30' THEN a.value END) value_30
, MIN(CASE a.pk5 WHEN '30' THEN b.cdVal01 END) cdVal01_30
, MIN(CASE a.pk5 WHEN '30' THEN b.cdVal02 END) cdVal02_30
, MIN(CASE a.pk5 WHEN '30' THEN b.cdVal03 END) cdVal03_30
, MIN(CASE a.pk5 WHEN '40' THEN a.value END) value_40
, MIN(CASE a.pk5 WHEN '40' THEN b.cdVal01 END) cdVal01_40
, MIN(CASE a.pk5 WHEN '40' THEN b.cdVal02 END) cdVal02_40
, MIN(CASE a.pk5 WHEN '40' THEN b.cdVal03 END) cdVal03_40
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY pk5 ORDER BY reg_date DESC) rn
, pk5
, value
FROM valueTable
WHERE pk1 = :pk1
AND pk2 = :pk2
AND pk3 = CASE WHEN pk5 IN ('30', '40') THEN pk3 ELSE :pk3 END
AND pk4 = CASE WHEN pk5 IN ('30', '40') THEN pk4 ELSE :pk4 END
AND pk5 IN ('10', '20', '30', '40')
) a
LEFT JOIN codeTable b
ON b.dtl_cd = a.pk5
AND b.code = 'PK5'
WHERE a.rn = 1
;
by mbj1
[2024.07.19 10:06:51]
감사합니다!!!
) 버튼을 클릭하여 작성 하시면 됩니다.