민아의 기록일지

마스터 테이블을 A라고 한다면,

SN 과 ROTN 을 PK 로 가지고 있고, 다른 컬럼들이 존재한다고 하자. 이 컬럼들을 서브 테이블이 FK로 가지고 있다.

서브테이블을 B라고 한다면,

B테이블은 A테이블의 SN과 ROTN 컬럼을 참조하고있다. 

하지만, A 테이블에는 있고, B테이블에는 없는 경우가 있다.

내가 원하는 결과는 B에 있으면 있는 row의 카운트를. 없으면 0 이라고 나오는걸 원했다. 

처음에는 inner join 을 이용했는데. 생각해보니 그렇게 한다는건, 가정이 on 에 넣은 컬럼들이 A테이블, B테이블에 일치한 것만 가져오기 때문에. 만약 B 테이블에 없는 값을 조회한다면 count가 0이 아닌, NULL 이 나올 것이다.

오류 쿼리! (INNER JOIN)

SELECT
    if(COUNT(1) >= d.NOPE, 'UNAVAILABLE', 'AVAILABLE') AS availableCheck,
    a.SN,
    a.ROTN
FROM
    info AS a
    INNER join
    dtl AS d
    ON a.SN=d.SN AND a.ROTN=d.ROTN
    GROUP BY a.SN, a.ROTN, d.NOPE

이렇게 했을 때, 두 테이블에 모두 존재하는 애들만 조회가 되었다. 하지만 나는 모든 A에 있는 데이터에 대한 B 테이블 결과를 보고싶은거다!

수정한 쿼리!! (RIGHT OUTER JOIN, count(컬럼명))

SELECT
	if(COUNT(a.SN) >= d.NOPE, 'UNAVAILABLE', 'AVAILABLE') AS availableCheck,
	d.SN,
	d.ROTN,
	d.NOPE
FROM
	info AS a
	RIGHT OUTER join
	dtl AS d
	ON a.SN=d.SN AND a.ROTN=d.ROTN
GROUP BY a.SN, a.ROTN, d.NOPE

info (B테이블) 에 없어도 나올 수 있도록, RIGHT OUTER JOIN 을 사용하였고, GROUP BY 는 같다.

여기서 주목할 것은, COUNT 함수에 B테이블에 몇개 있는지를 알고 싶은 것이기때문에, 보통은 count(1) 이렇게 넣어서 항상 생각햇는데, 그건 나의 너무 좁은 생각이었던거다! 왜 컬럼을 넣을 생각을 못했을까.

아무튼, 그래서 COUNT(B테이블컬럼) 을 넣었더니, 원하는 결과가 나왔다!!!!

mysql 에서는 아주 간편하게 limit 함수를 이용해서 몇개의 데이터만 가지고 왓엇는데, rownum 이라는 함수를 사용해서인지 이유는 아직 모르겟다... 아직 더 자세히 공부를 하지는 못햇다. 

하지만, 일단 원하는 rownum의 범위로 검색

SELECT
 * 
FROM(
    SELECT 
        sub.*, 
        ROWNUM AS "OFFSET" 
    FROM
        (SELECT 
            WORKER_NAME AS "workerName",
            WORKER_TEL AS "workerTel",
            TIMELINE AS "timeline",
            COMUTE_TYPE AS "commuteType",
            TO_CHAR(TIMELINE, 'YYYY-MM-DD') AS "dateInfo"
        FROM 
            ATTENDANCE_INFO
            WHERE IS_STATISTICS = 0
            GROUP BY TO_CHAR(TIMELINE, 'YYYY-MM-DD'), WORKER_NAME, WORKER_TEL, TIMELINE, COMUTE_TYPE
            ORDER BY WORKER_NAME, TO_CHAR(TIMELINE, 'YYYY-MM-DD')
    ) sub)
    WHERE ROWNUM <= 80 
    AND OFFSET >= 30;

여기서

ROWNUM을 OFFSET으로 ALIAS 로 지정해두고, WHERE 절에 사용된 ROWNUM 은 sub 에서 select된 ROWNUM 이 아닌, 맨 마지막에 SELECT * 에서 선택되어지는 ROW들의 NUM 이라고 생각하면 된다.

그리고 그 안에서의 ROWNUM 은 OFFSET으로 지정된거다. 그래서 OFFSET이라는 변수에 저런식으로 지정해주면 된다!

SELECT 
	sub.*, 
	ROWNUM AS cntnt 
FROM
	(SELECT 
		WORKER_NAME AS "workerName",
		WORKER_TEL AS "workerTel",
		TIMELINE AS "timeline",
		COMUTE_TYPE AS "commuteType",
		TO_CHAR(TIMELINE, 'YYYY-MM-DD') AS "dateInfo"
	FROM 
		ATTENDANCE_INFO
		WHERE IS_STATISTICS = 0
		GROUP BY TO_CHAR(TIMELINE, 'YYYY-MM-DD'), WORKER_NAME, WORKER_TEL, TIMELINE, COMUTE_TYPE
		ORDER BY WORKER_NAME, TO_CHAR(TIMELINE, 'YYYY-MM-DD')
	) sub

ROWNUM 이라는 컬럼을 사용하면, 순번이 row의 번호가 붙어서 나오게된다.

GROUP BY  를 활용해서 조건으로 그룹지어 조회할 수 있다. 여기서 포인트는, 만약 SUM 이런걸 원한다면 select 에 컬럼명으로 sum(원하는 합산의 컬럼명) 으로 적고 group by 조건에는 넣어주지않아도된다. 하지만 다른 표시하고싶은 컬럼들은 모두 group by 에 넣어줘야한다. (이게 mysql 과 다른점이다.)

TO_CHAR 을 활용하여, 숫자나 날짜를 원하는 형태로 문자화 시켜서 출력할 수 있다.

쿼리 결과

항상 까먹어서 매번 찾아보길래 기록해두기!!!

sdet_info 테이블에는 존재하는데 prep_sdet_info 테이블에는 존재하지않는 정보들을 가져오고자 함

공통된부분 + 왼쪽테이블에만 존재하는거

Inner join은 공통된 부분만 가져오는데 이거는 일단 left 이기 때문에 왼쪽에 먼저 정의한 테이블이 중심인데, 그 중심이 되는 테이블에서 공통되는 부분과 중심 테이블에만 있는 부분이 함께 출력된다. 

SELECT 
	*
FROM 
	sdet_info as s 
	left outer join 
	prep_sdet_info as ps 
	on ps.KEDI_CD=s.KEDI_CD
	where stus='정상'
    and
    s.KEDI_CD IS NULL;

on : 부분에는 공통적으로 들어가는 부분에 대한 조건을 넣어준다 (다른 join과 같은 사용용도)

하지만, 나는 공통된 부분은 제외하고 싶었기 때문에 where 절에 중심테이블인 sdet_info 에만 존재하는 컬럼으로 조건을 넣어줬다. 그래서 sdet_info에만 있는 데이터들만 나왔다.

prep_sdet_info 테이블 의 정보가 sdet_info 테이블 에도 있고 sdet_info의 STUS 컬럼이 '정상' 인 row 들만 update하게 하고 싶었다. 그래서 내가 먼저 짠 쿼리는......

바로!!!!

UPDATE 
	prep_sdet_info as ps
	,sdet_info AS s 
SET ps.APRV_CNT = 1 
WHERE ps.KEDI_CD=s.KEDI_CD 
    AND ps.SDET_NM=s.SDET_NM 
    AND ps.GDE=s.GDE 
    AND ps.CLA=s.CLA 
    AND ps.SDET_NO=s.SDET_NO
    AND s.STUS='정상' 
    AND ps.APRV_CNT=0;

그런데!!!!! 역시 무엇인가 불안했다. 그래서 물어봤다!! update문은 신중해야하기에..... 당연히 이 쿼리도 작동은 하지만 직관적이지않아 바로 해석이 되지 않는다 했다. 코드는 심플하고 직관적이게!!! 해야하니깐

그래서 알려주신 방법!!!!

UPDATE 
	prep_sdet_info as ps 
SET ps.APRV_CNT = 1 
WHERE 0 < (
    SELECT 
        COUNT(*)
    from  
        sdet_info AS s 
    WHERE 
        ps.KEDI_CD=s.KEDI_CD 
        AND ps.SDET_NM=s.SDET_NM 
        AND ps.GDE=s.GDE 
        AND ps.CLA=s.CLA 
        AND ps.SDET_NO=s.SDET_NO 
        AND s.STUS='정상')
AND ps.APRV_CNT=0;

결국 이 개념을 내가 잘 이해하지 못해서 매번 헷갈리는거다. 

즉, 이 UPDATE문이 실행되는 방식은 일단, 먼저 UPDATE시키고자하는 테이블을 기준으로 한 row의 데이터를 선택하고 WHERE 조건문에 있는 서브쿼리의 조건에 맞는지를 확인해서, COUNT(*)를 해보고 0보다 크면 (즉, 조건에 해당하는 ROW 이면) UPDATE문을 실행하는거다. 

한 ROW 씩 읽고 조건에 맞으면 UPDATE가 되고 안되고하면서 넘어가는거다. 

HeidiSQL (mariaDB) 

root로 접속한 후,

생성한 데이터베이스를 새로운 session 에 넣어주자. '사용자' 에는 아이디를 '암호'에는 비밀번호를 설정한 대로 넣어준다. 그러면 데이터베이스 쪽에 삼각형을 누르면 방금 생성했던 데이터베이스가 있는 걸 볼 수 있다. 그걸 선택해서 열어준다.

쿼리를 실행 후, 새로고침을 눌러주면 데이터베이스에 테이블이 생성되있는 걸 볼 수 있다!!!!!

2가지의 방법이 있음

첫번째, MYSQL 에 있는 DATYOFWEEK 함수를 CASE와 함께 이용하는 방법!

DAYOFWEEK 자체는 숫자로 반환해주는 함수이다. 그래서 반환되는 숫자마다 CASE로 조건문을 걸어줘서 요일을 한글로 설정해주는 방법이다. 근데 이 방법은 한 쿼리에 여러 날짜에 대한 요일을 구할때는 너무 불편했다. 그래서 이 CASE문 자체를 FUNCTION 을 만들려다가 다시 찾아보니 더 쉬운방법이 있었다!!!!!

두번째, SUBSTR을 이용한 방법!!

'일월화수목금토' 라는 문자열에서 DAYOFWEEK 함수에서 반환되는 숫자에 해당하는 자리에서 한글자만 잘라내는거다.

참고로 _UTF-8이라고 앞에 적어둔 것은, MYSQL 인코딩이 한글로 되어있지 않은경우 에러가 나기때문에 해놓은 것 같다. 만약 UTF-8로 인코딩이 이미 설정되어있다면, 굳이 저건 적지 않아도 된다.

원리만 이해하면 참 쉬운데...... 나는 언제 이렇게 머리쓰는 방법을 터득할 수 있을까????

참고: https://okkks.tistory.com/1086

기간에 대한 조건절을 먼저 실행하고 그 다른 조건을 다음으로 실행하고 싶었다. 하지만 기간에 대한 조건은 OR 조건문이었고, 그러다보니 모든 데이터가 선택되어서 결과가 나와버렸다.

예를들면, '연주'이면서 (AND) 기간이 시작과 종료일에 둘중에(OR) 들어있는 데이터가 선택되어야한다. 하지만, 무조건 기간에 OR가 들어가있다보니 그기간중에 둘중에 들어있는 데이터면 '연주' 가 아니여도 출력이 되었다. 

그렇다면, 여기서 내가 OR조건을 먼저 실행해주고 그 다음 AND조건을 실행해주는 순서로 바꿔주면 다를까해서 바꿔보았다. 

먼저 실행되고자하는 조건에 괄호로 묶어주면 일단 먼저 그 조건이 실행된다. 그래서 OR조건에 해당하면서 연주에도 해당되는 데이터가 실행되었다!! 

참고: https://goguri.tistory.com/460