오늘 한 일
- DB 연결 수정: 프로젝트 DB 컬럼명 수정, 쿼리 일부 수정
- 얄팍한 코딩사전 MYSQL SQL 기초 강의 수강
DB 연결 수정
어제까지 사용자 목록 조회 및 검색 기능을 만들었는데, 알고보니 연결해야 할 테이블이 아닌 다른 테이블을 참조하고 있다는 것을 어제 알았다.
sql.xml 수정
사용자 목록 조회 시 left join을 사용할 필요가 없지 않냐는 질문을 들어서 오늘 sql 기초를 공부했다. mysql의 join에 대해 알아보니 left join은 기본적으로 outer join으로, 즉 2개 테이블 조인할 때 첫 번째 테이블을 기준으로 두번째 테이블에 값이 없어도 모두 가져오는 join 방식이었다.
사용자 정보를 이용해 부서, 직급 정보에서 이름값을 가져올 때, 사용자 정보에 부서, 직급 코드가 반드시 존재하기 때문에 left join을 사용할 필요가 없다. 공부한 내용을 바탕으로 기존 sql query를 더 짧게 수정했다. 예를 들면 다음과 같다.
<select id="selectUserListTotCnt" parameterType="searchVO" resultType="int">
SELECT COUNT(EMP_NO) totcnt
FROM EMPLOYEE AS EMP
JOIN DEP
ON EMP_DEPT = DEP_CODE
JOIN POS
ON EMP_POSITION = POS_CODE
WHERE 1=1
<if test="searchKeyword != null and searchKeyword != ''">
<choose>
<when test="searchCondition == 0">
AND EMP_NAME LIKE CONCAT('%', #{searchKeyword},'%')
</when>
<when test="searchCondition == 1">
AND DEP_NAME LIKE CONCAT('%', #{searchKeyword},'%')
</when>
<when test="searchCondition == 2">
AND POS_NAME LIKE CONCAT('%', #{searchKeyword},'%')
</when>
</choose>
</if>
</select>또한 출장일 기준으로 출장 목록을 검색할 때, searchKeyword로 들어온 출장일이 출장 시작일보다는 뒤, 출장 종료일보다는 앞서야 한다는 조건이 필요했는데, 기존에는 < 부등호를 사용해 2줄로 작성했었다. 이번에 공부해보니 BETWEEN A AND B라는 명령어가 있어 한 줄로 정리할 수 있었다.
SELECT COUNT(*) totcnt
FROM BUSINESSTRIP
WHERE 1=1
AND "출장일" BETWEEN TRIP_START_DATE AND TRIP_END_DATE출장 관련자 테이블 추가 계획
다음주부터 출장 테이블에 출장자, 결재자, 수신자의 id값 컬럼을 삭제하고 출장 관련자 테이블BUSINESSTRIP_ROLES AS BTR에 저장할 수 있도록 프로젝트를 바꿔야 한다. 조회할 때도 최대 5개 테이블의 값을 JOIN해서 가져와야 하는데, 검색 기능도 생각해서 서브 쿼리를 잘 짜야한다. 정말 쉽지 않을 것 같다...
구체적 계획
- 출장 관련자 테이블에 사용자 테이블값, 출장 번호 참고해서 더미데이터 삽입
- 출장 상세조회 시 출장 번호 기준으로 출장 관련자-사용자-부서, 직급 테이블 정보 불러오게 수정
- 출장 VO에 출장 관련자 VO List도 만들기 (출장 비용 VO List 참고)
- 사용자 정보 조회 결과에서 개별 사용자 선택 시 출장 관련자 정보 만들어서 조회 페이지에 전달
- 출장 정보 CUD 시 출장 관련자 테이블도 같이 조작
MYSQL 기초 강의 수강
현재 서브 쿼리, join 등 sql을 사용해야 하기도 하고, 추후 정보처리기사에도 나오는 만큼 기초 강의를 한 번 들으면서 sql 공부를 시작하려고 얄팍한 코딩사전의 sql 기초 강의를 들었다. 강의를 보면서 내가 만든 테이블을 가지고 쿼리를 연습했다. 이전에 하고 싶다고 생각은 방법을 몰랐던 쿼리들을 일부 할 수 있게 되었다.
예시: 각 출장마다 출장비용의 합을 조회해 계산한 합이 0 이상인 비용합만 출력
SELECT BT.BT_ID, (SELECT SUM(PRICE) FROM BUSINESSTRIP_EXPENSES AS BTE WHERE BT.BT_ID = BTE.BT_ID) AS TOTALPRICE FROM BUSINESSTRIP AS BT HAVING TOTALPRICE>0;
특정 날짜 사이에 0 이상 값 지출된 교통비 가져오기
SELECT TRIP_START_DATE,TRIP_END_DATE, PRICE AS '교통비', EXPENSE_DETAIL FROM BUSINESSTRIP_EXPENSES AS BTE JOIN BUSINESSTRIP AS BT ON BTE.BT_ID = BT.BT_ID WHERE DATE("2021-12-27") BETWEEN TRIP_START_DATE AND TRIP_END_DATE AND EXPENSE_TYPE=0 AND PRICE>0;
- 전에 하려다 포기했던 출장일수 출력 기능 구현
SELECT
BT_ID
, LOCATION
, AUTHOR_ID
, TRAVELER_ID
, TRIP_START_DATE
, TRIP_END_DATE
, CONCAT(DATEDIFF(TRIP_END_DATE, TRIP_START_DATE)+1, "일") AS TRIP_PERIOD
FROM BUSINESSTRIP<!-- 출장 목록 조회 페이지 -->
<td align="center" class="listtd"><c:out value="${result.tripStartDate} ~ ${result.tripEndDate} (${result.tripPeriod})"/> </td>