내 서재 컬럼에는 오리지널 도서, 일반도서 2가지의 카테고리가 나누어져있으며
이 두 도서는 전혀 다른테이블에 등록되어있다.
이 두 테이블은 컬럼도 조금씩 다르기때문에 하나로 합칠수 없었다.
나는 이 두 테이블을 합쳐 별점의 평균 or 조회수 or 등록 순 3가지로 정렬하고 싶었다.
여기서 몇가지 문제가 발생했다.
2번은 금방 해결했다.
1번컬럼/2번컬럼 "컬럼명"을 적어주면 평균이 구해지며 컬럼의 값이 null이거나 0이라 계산이 되지 않으면
DECODE(컬럼2, 0, 0, 컬럼1/컬럼2) "컬럼명"
으로 적어주면 된다.
1번은 저녁 내내 구글링을 해서 나는 몇가지 조건으로 테이블을 출력하는 쿼리문을 만들어 낼수있었다.
이게 내가 테이블을 연결하고자 하는 내 서재 테이블이다.
보다기피 카테고리가 나누어져있는데 카테고리 별로 중복 book_no가 생길수있다. 그래서 반드시 카테고리 별 책 번호를 기준으로 연결해주어야 한다.
select library_no,nor_book_no,DECODE(scorecount, 0, 0, score/scorecount) "avg", view_count "count", book_title "title",cover_rename "pic2" from library left join normal_BOOK on book_no=nor_book_no where library.category='normal';
좀 복잡하지만 첫번쨰, library_no를 기준으로 내가 필요한 정보인 일반도서 번호, 별점평균, 카운터값, 책제목, 책표지 이름을 해당 쿼리문으로 출력했다. 이 쿼리문으로 만들어진 테이블을 똑같은 방식으로 만든
select library_no,ori_book_no , DECODE(score_count, 0, 0, score/score_count) "avg1" ,book_title "title1", view_count "count1",corver_rename "pic1" from library left join ORIGIN_BOOK on book_no=ori_book_no where library.CATEGORY = 'origin'
오리지널 도서의 테이블과 library_no를 기준으로 연결해주면 된다
이것이 가능해야 내가 바란 조건의 정렬이 가능할것이다.
그것은 어렵지 않았다, 위에 표를 보면 나는 의도적으로 겹치는 컬럼의 이름을 "컬럼명"으로 다르게 정해주었는데
두 컬럼을 join으로 합칠것이라면 "a컬럼명"||''||"b컬럼명"
을 사용해주면 된다. ||사이는 홑따옴표이며 ,등을 넣어 값을 구분해줄수도 있다, 나의경우 두 컬럼이 합쳐져도 겹쳐지지는 값이 없으니 ||사이에는 아무값도 넣어주지 않았다, 컬럼명에는 ""를 반드시 해주어야 제대로 인식한다.
이렇게 해서 내가 원하는 테이블을 출력할수있었다.
select library_no,book_no,category,"avg"||''||"avg1" "avg", "count"||''||"count1" "count","title1"||''||"title" "title","pic1"||''||"pic2" "picName" from library
left join(select library_no,nor_book_no,DECODE(scorecount, 0, 0, score/scorecount) "avg", view_count "count", book_title "title",cover_rename "pic2" from library left join normal_BOOK on book_no=nor_book_no where library.category='normal')
using(library_no)
left join(select library_no,ori_book_no , DECODE(score_count, 0, 0, score/score_count) "avg1" ,book_title "title1", view_count "count1",corver_rename "pic1" from library left join ORIGIN_BOOK on book_no=ori_book_no where library.CATEGORY = 'origin') using (library_no)
WHERE member_id = 'admin' order by library_no desc;
복잡해 보이나 구조는 간단하다
select 출력을 원하는 칼럼1,2,3 from 테이블명 left join(위에서 내가 library_no를 기준으로 출력하고자 만든 쿼리문1) left join(마찬가지로 똑같이 만든 쿼리문2) where 컬럼명 = '찾는값' order by 기준컬럼 desc;(일부러 역순정렬)
정렬할 컬럼명은 "avg1||''||"avg2" "avg3"
<- 만약 이 컬럼값으로 정렬하고 싶다면 order by "avg3"
로 해주어야 한다.
나는 title컬럼을 검색하고 싶었다.
그래서 where절에 title = '검색어'
를 했는데 2테이블이 합쳐진 검색값이 나오지 않았다.
where절은 정확한 컬럼의 이름이 필요한것 같았다.
"title1"||''||"title2 "title"
컬럼의 title의 (그러니까 join한 두 테이블의 title1컬럼 + title2컬럼 한 컬럼을 검색하고 싶다면)
("title1" like '%||#{searchValue}||%' or "title2" like '%||#{searchValue}||%')
이렇게 where의 쿼리문 조건을 써주면 두 컬럼을 합쳐서 검색이 된다.
<!-- 내 서재 목록 -->
<select id="selectOneMemberLibrary" resultMap="libraryResultMap">
select library_no,book_no,category,"avg"||''||"avg1" "avg", "count"||''||"count1" "count","title1"||''||"title" "title","pic1"||''||"pic2" "picName" from library left join (select library_no,nor_book_no,DECODE(scorecount, 0, 0, score/scorecount) "avg", view_count "count", book_title "title",cover_rename "pic2" from library left join normal_BOOK on book_no=nor_book_no where library.category='normal' and status like 'Y%') using(library_no)
left join(select library_no,ori_book_no , DECODE(score_count, 0, 0, score/score_count) "avg1" ,book_title "title1", view_count "count1",corver_rename "pic1" from library left join ORIGIN_BOOK on book_no=ori_book_no where library.CATEGORY = 'origin' and status like 'Y%' and BOOK_PERMISSION like 'Y%') using (library_no)
<where>
member_id = #{memberId}
<if test="category.toString() == 'origin'">
and category = 'origin'
<if test="searchValue != null">
and "title1" like '%'||#{searchValue}||'%'
</if>
</if>
<if test="category.toString() == 'normal'">
and category = 'normal'
<if test="searchValue != null">
and "title" like '%'||#{searchValue}||'%'
</if>
</if>
<if test="category.toString() == 'all'">
<if test="searchValue != null">
and ("title1" like '%'||#{searchValue}||'%' or "title" like '%'||#{searchValue}||'%')
</if>
</if>
</where>
<if test="step.toString() == 'all'">
order by library_no desc</if>
<if test="step.toString() == 'star'">
order by "avg" desc</if>
<if test="step.toString() == 'view'">
order by "count" desc</if>
</select>
where 태그와 if를 이용해서 동적쿼리를 활용해 어떤 검색값이나 조회값이 들어온다고 해도 대응할수 있도록 작성했다.
이렇게 작성하면 코드를 하나만 사용해도 변경되는 값에 전체 대응할수있게된다.