꿈꾸는 개발자, DBA 커뮤니티 구루비

서브쿼리 질문 드립니다.

by 철산92 [MySQL] 서브쿼리 TIMESTAMPDIFF [2018.01.19 14:19:39]

지금 작성한 쿼리보다 효울적인 쿼리를 알고 싶어서 조언을 구하고자 질문 드립니다.

user테이블 입니다.

userPhone UserName

***-****-****   ***
***-****-****   ***
***-****-****   ***
***-****-****   ***
***-****-****   ***
***-****-****   ***

commute테이블 입니다.

***-****-****   ***   2018-01-12 09:54:18   2018-01-12 18:54:18
***-****-****   ***   2018-01-13 09:49:24   2018-01-13 22:00:24
***-****-****   ***   2018-01-14 09:00:38   2018-01-14 20:00:38
***-****-****   ***   2018-01-15 08:01:21   2018-01-15 19:01:21
***-****-****   ***   2018-01-16 07:17:14   2018-01-16 16:18:14
***-****-****   ***   2018-01-17 14:40:36   2018-01-17 16:04:21
***-****-****   ***   2018-01-16 17:38:28   2018-01-16 18:02:56
***-****-****   ***   2018-01-17 14:24:18   2018-01-17 14:37:24
***-****-****   ***   2018-01-18 10:18:07
***-****-****   ***   2018-01-18 13:21:10   2018-01-18 13:25:15
***-****-****   ***   2018-01-18 13:42:14
***-****-****   ***   2018-01-19 10:23:57

저가 작성한 쿼리 입니다.

SELECT @ROWNUM := @ROWNUM + 1 ROWNUM , 
       userName , 
       userPhone,
       (SELECT InTime 
         FROM commute 
        WHERE DATE(commuteInTime) = '2018-01-12' 
              AND commutePhone = AAA.userPhone 
       ) as commuteInTime , 
       (SELECT commuteOutTime 
         FROM commute 
        WHERE DATE(commuteOutTime) = '2018-01-12' 
              AND commutePhone = AAA.userPhone 
       ) as commuteOutTime , 
       TIMESTAMPDIFF(HOUR, 
       (SELECT commuteInTime 
         FROM commute 
        WHERE DATE(commuteInTime) = '2018-01-12' 
              AND commutePhone = AAA.userPhone 
       ) ,
       (SELECT commuteOutTime 
         FROM commute 
        WHERE DATE(commuteOutTime) = '2018-01-12' 
              AND commutePhone = AAA.userPhone  
       )) as subHour , 
       TIMESTAMPDIFF(MINUTE, 
       (SELECT commuteInTime 
         FROM commute 
        WHERE DATE(commuteInTime) = '2018-01-12' 
              AND commutePhone = AAA.userPhone 
       ) ,
       (SELECT commuteOutTime 
         FROM commute 
        WHERE DATE(commuteOutTime) = '2018-01-12' 
              AND commutePhone = AAA.userPhone  
       ))%60 as subMinute 
  FROM user AAA,(SELECT @ROWNUM := 0) R

쿼리 결과 입니다.

seq userName userPhone commuteInTime commuteOutTime subHour subMinute

1 *** ***-****-**** 2018-01-12 09:54:18 2018-01-12 18:54:18 9 0
2 *** ***-****-**** null null null null
3 *** ***-****-**** null null null null
4 *** ***-****-**** null null null null
5 *** ***-****-**** null null null null

더 좋은 쿼리가 생각나지 않아서 질문 드립니다. 알려주시면 감사 하겠습니다.

by 우리집아찌 [2018.01.19 14:53:54]

쿼리결과에

2018-01-12 만 출력하는 건가요?

왜 로우가 5개인가요?

by 마농 [2018.01.19 14:57:44]

SELECT @ROWNUM := @ROWNUM + 1 AS ROWNUM
     , a.userName
     , a.userPhone
     , b.commuteInTime
     , b.commuteOutTime
     , TIMESTAMPDIFF(hour  , b.commuteInTime, b.commuteOutTime)    AS subHour
     , TIMESTAMPDIFF(minute, b.commuteInTime, b.commuteOutTime)%60 AS subMinute
  FROM user a
 CROSS JOIN (SELECT @ROWNUM := 0) r
  LEFT OUTER JOIN commute b
    ON b.commutePhone = a.userPhone
   AND b.commuteInTime >= '2018-01-12 00:00:00'
   AND b.commuteInTime <= '2018-01-12 23:59:59'
;

by 철산92 [2018.01.19 15:27:30]

답변 감사합니다. 원하는 결과 출력 됩니다.

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변