[MySql] Group By

[MySql] group by

• 특정 컬럼을 그룹화
• 사용법
  • 컬럼 그룹화
  • SELECT 컬럼 FROM 테이블 GROUP BY 그룹화할 컬럼;
  • 조건 처리 후에 컬럼 그룹화
  • SELECT 컬럼 FROM 테이블 WHERE 조건식 GROUP BY 그룹화할 컬럼;

 

Final Project에서 마이페이지 무비로그에 들어가면 관람한 영화 목록을 모두 출력

문제는 아래 사진처럼 예매수량에 맞춰 같은 영화도 복수로 출력됨

 

 

MyBatis SQL문 → select 반환 값이 list

<!--마이페이지 무비로그 조회-->
    <select id="selectMovieLog" parameterType="int" resultType="MyPage">
        SELECT
            b.book_the_name AS theater,
            b.book_adult_cnt AS adult,
            b.book_youth_cnt AS youth,
            st.scrt_date AS date,
            st.scrt_stime AS begin,
            st.scrt_etime AS endtime,
            s.scr_name AS screen,
            m.m_name AS title,
            m.m_enname AS engtitle,
            m.m_age_grd AS grade,
            m.m_photo AS poster
        FROM
            user_tb u JOIN payment_tb p ON u.user_pk = p.user_pk
            JOIN booking_tb b ON p.payment_pk = b.payment_pk
            JOIN screentime_tb st ON b.scrtime_pk = st.scrtime_pk
            JOIN screen_tb s ON s.screen_pk = st.screen_pk
            JOIN movie_tb m ON st.movie_pk = m.movie_pk
        WHERE u.user_pk = #{user_pk};
    </select>

 

영화명이 같은 데이터는 하나만 출력되도록 영화명으로 group by (그룹화) 추가

<!--마이페이지 무비로그 조회-->
    <select id="selectMovieLog" parameterType="int" resultType="MyPage">
        SELECT
            b.book_the_name AS theater,
            b.book_adult_cnt AS adult,
            b.book_youth_cnt AS youth,
            st.scrt_date AS date,
            st.scrt_stime AS begin,
            st.scrt_etime AS endtime,
            s.scr_name AS screen,
            m.m_name AS title,
            m.m_enname AS engtitle,
            m.m_age_grd AS grade,
            m.m_photo AS poster
        FROM
            user_tb u JOIN payment_tb p ON u.user_pk = p.user_pk
            JOIN booking_tb b ON p.payment_pk = b.payment_pk
            JOIN screentime_tb st ON b.scrtime_pk = st.scrtime_pk
            JOIN screen_tb s ON s.screen_pk = st.screen_pk
            JOIN movie_tb m ON st.movie_pk = m.movie_pk
        WHERE u.user_pk = #{user_pk}
        GROUP BY title;
    </select>

 

SQL문 수정 후 의도한대로 출력