my sql 최신값 가져오기 쿼리 0 5 1,298

by 마이닝 [MySQL] [2018.10.10 17:57:59]


1.png (9,084Bytes)
2.png (4,377Bytes)

아래쿼리를 검색하면 이미지 1번과 같은 결과가 나옵니다.
이거를 sid 별로 ctime 기준 가장 최신데이터로 그룹핑하여 press1, oval11, blevel 까지 같이 뽑고싶습니다.
그런데 ctime은 맥스로 뽑으면되지만 나머지 3개의 최신데이터를 어떻게 가져와야할지 모르겠습니다. 도와주세요 ㅜ

* 이미지 1번쿼리
SELECT sid, ctime, press1, oval11, blevel
FROM sp_device_data                                        
WHERE sid = '0000000000273988'
AND cdate = '20181010'
ORDER BY ctime DESC 

                        
* 이미지 2번쿼리    (sid별 / 최신 ctime / ctime과 일치하는 press1, oval11, blevel 를 얻고싶습니다..)                               
SELECT sid, ctime,press1,oval11,blevel
FROM( 
  SELECT sid, ctime, press1, oval11, blevel
  FROM sp_device_data 
  WHERE sid in (SELECT sid FROM sp_device_user WHERE uid = 'be8b2ac7f92e4a7a92dc68c06a3a8025')
  AND cdate = '20181010'
) AS A 
GROUP BY sid
ORDER BY ctime DESC 

 

* 아래 처럼 해서 결과를 도출했더니, sql 문에서는 잘돌아가지만 실제 프로그램상에선 에러가 생기더라구요..

Illuminate\Database\QueryException: SQLSTATE[42000]: Syntax error or access vi
com.google.gson.JsonSyntaxException: java.lang.IllegalStateException: Expected BEGIN_OBJECT but was STRING at line 1 column 1 path $ 라는.

다른방법이 필요합니다 ㅜㅜ

 SELECT t0.sid sid, STR_TO_DATE(t0.ctime, '%Y-%m-%d %H:%i:%s') ctime, 
                      (SELECT press1 FROM sp_device_data WHERE cdate = t0.cdate AND sid = t0.sid order by ctime desc limit 1) press1,
                      (SELECT oval11 FROM sp_device_data WHERE cdate = t0.cdate AND sid = t0.sid order by ctime desc limit 1) oval11,
                      (SELECT blevel FROM sp_device_data WHERE cdate = t0.cdate AND sid = t0.sid order by ctime desc limit 1) blevel
                      FROM (
                        SELECT sid, STR_TO_DATE(max(ctime), '%Y-%m-%d %H:%i:%s') ctime, cdate
                        FROM sp_device_data
                        WHERE sid in (SELECT sid FROM sp_device_user WHERE uid = 'be8b2ac7f92e4a7a92dc68c06a3a8025')
                        AND cdate = '20181010'
                        GROUP BY sid) t0;

 

 

by 우리집아찌 [2018.10.10 18:10:29]

  max() keep()  또는

, row_number() over() 쓰시면 될것 같은데요.

my-sql에서는 max() keep() 안될것 같은 row_number() over() 사용하세요

select t.* 
 from (select  a.* 
             , row_number() over(partition by sid  order by ctime desc )  rn
       from table a
       ) t
  where rn = 1 

 


by 우리집아찌 [2018.10.10 18:10:57]

ctime 이 동일한것이 두개 있을경우 order by 조건을 에 추가하시면 됩니다.


by 마이닝 [2018.10.10 18:32:51]

답변감사합니다!  하지만 mysql 버전이 낮아서 그런지.. ROW_NUMBER 이 지원되지 않는것같습니다 ㅜㅜ


by 마농 [2018.10.10 18:32:26]

2번 쿼리는 MySQL 에서만 허용되는 GROUP BY 구문입니다.
 - 표준 방식이 아닙니다. 다른 DB 에서는 에러날 구문입니다.
 - 그래도 비표준 방식을 사용하고자 한다면?
 - ORDER BY 구문의 위치만 바꿔주시면 됩니다.
 - ORDER BY 구문을 인라인뷰 안으로 넣으세요.
 

-- 1. Order BY 위치 변경 - 비표준
SELECT sid, ctime, press1, oval11, blevel
  FROM (SELECT sid, ctime, press1, oval11, blevel
          FROM sp_device_data 
         WHERE sid IN (SELECT sid
                         FROM sp_device_user
                        WHERE uid = 'be8b2ac7f92e4a7a92dc68c06a3a8025'
                       )
           AND cdate = '20181010'
         ORDER BY sid, ctime DESC
        ) a
 GROUP BY sid
;
-- 2. 분석함수 사용이 가능하다면? (8.0 이상)
SELECT sid, ctime, press1, oval11, blevel
  FROM (SELECT sid, ctime, press1, oval11, blevel
             , ROW_NUMBER() OVER(PARTITION BY sid ORDER BY ctime DESC) rn
          FROM sp_device_data 
         WHERE sid IN (SELECT sid
                         FROM sp_device_user
                        WHERE uid = 'be8b2ac7f92e4a7a92dc68c06a3a8025'
                       )
           AND cdate = '20181010'
        ) a
 WHERE rn = 1
;
-- 3. Max 쿼리와 셀프 조인
SELECT b.sid, b.ctime, b.press1, b.oval11, b.blevel
  FROM (SELECT sid, cdate
             , MAX(ctime) ctime
          FROM sp_device_data 
         WHERE sid IN (SELECT sid
                         FROM sp_device_user
                        WHERE uid = 'be8b2ac7f92e4a7a92dc68c06a3a8025'
                       )
           AND cdate = '20181010'
         GROUP BY sid, cdate
        ) a
 INNER JOIN sp_device_data b
    ON a.sid   = b.sid
   AND a.cdate = b.cdate
   AND a.ctime = b.ctime
;

 


by 마이닝 [2018.10.10 19:52:43]

감사합니다.  항상 많은 도움받고있습니다.  사용하는 mysql 버전이 낮아서 분석함수 사용이 안되서 3번째 알려주신 방법으로 했습니다. 저렇게 셀프조인하면 쿼리 여러번 안날려도 가져올수있군요.. 감사합니다!

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