그룹의 가장최근등록건의대한 value값 0 8 5,434

by mbj1 [MySQL] [2024.07.16 14:55:44]


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]

감사합니다!!!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입