그룹 기능을 사용하여 결과 집합을 필터링할 때 발생하는 몇 가지 문제와 해결 방법 [권장: mysql 비디오 튜토리얼]
1.
테이블이 두 개 있습니다기사 테이블(일대다 메시지 테이블)t_posts: oid, post_name
메시지 테이블(다대일 기사 테이블)
t_comment: oid,posts_id, msg_content, create_time
2. 요구사항 분석
기사별 최신 답변 내용 조회3.SQL writing
select tp.oid, tp.posts_name, tc.msg_content, tc.create_time from t_posts tp left join t_comment tc on tp.oid = tc.posts_id group by tp.oid having create_time = max(create_time)
기사 A, B가 있다고 가정합니다(데이터베이스에 답변 기록 순서는 다음과 같습니다). 다음과 일치)
<p>A有一个回复记录时间为: 2019-09-10 <br>A有一个回复记录时间为: 2019-09-11 <br>B有一个回复记录时间为: 2019-09-01 <br>B有一个回复记录时间为: 2019-09-09<br></p>위의 SQL을 실행하면 결과 집합에서 많은 수의 레코드가 손실되고 결과가 잘못되었음을 알 수 있습니다. 데이터를 쿼리한 결과 mysql이 group by 즉, 먼저 그룹화한 후 필터링한 후 실행되지만 메시지 레코드가 2개 이상이므로
그룹화 후 결과 세트는 각 메시지의 첫 번째 메시지만 그룹화 후 레코드 정보로 가져옵니다. 이번에 create_time = max(create_time)
을 사용하면 max(create_time)는 현재 그룹화의 최대 시간
그래서 위의 SQL은 결과 집합이 손실됩니다
4. 그룹화 후 병합될 것이라는 것을 알고 있으므로 SQL을 변환
중복된 결과 집합은 가장 작은 rownum을 갖는 것이므로 다음과 같이 SQL을 수정할 수 있습니까??
select tp.oid, tp.posts_name, tc.msg_content, tc.create_time from t_posts tp left join t_comment tc on tp.oid = tc.posts_id group by tp.oid having create_time = max(create_time) -- 下面的是新增的sql order by tc.create_time desc실행 후 여전히 작동하지 않는 것을 발견했습니다. 이는 order by가 group by & had 이후임을 증명합니다
나중에 생각해보니, order by를 직접 사용하여 최적화하는 것은 어떨까요? 그룹화된 결과?
create_time = max(create_time)
select tp.oid, tp.posts_name, tc.msg_content, tc.create_time from t_posts tp left join t_comment tc on tp.oid = tc.posts_id group by tp.oid order by tc.create_time desc결과 집합 오류는 그룹화 결과에 영향을 주지 않습니다. 중복된 결과 집합은 여전히 rownum 최소 그룹화에 따라 병합된 다음 Sorting에 있습니다.
5. 최종 수정 버전
order by는 group by에만 영향을 미치기 때문에, group by 이전에 결과 집합을 정렬한 다음 그룹화하는 것이 가능합니까?select * from ( select tp.oid, tp.posts_name, tc.msg_content, tc.create_time from t_posts tp left join t_comment tc on tp.oid = tc.posts_id order by tc.create_time desc ) t group by t.oid아직 작동하지 않는 것을 발견했습니다. 그런데 실제로는 하위 쿼리가 먼저 정렬되어 있습니다쿼리(설명) 후 하위 쿼리의 순서가 최적화된 것으로 나타났습니다. 해결 방법:
- 하위 쿼리에 제한 99999를 사용하세요
- 서브 쿼리에서 where 조건을 사용하세요. create_time = (oid로 t_comment 그룹에서 max(create_time) 선택)
-
select * from ( select tp.oid, tp.posts_name, tc.msg_content, tc.create_time from t_posts tp left join t_comment tc on tp.oid = tc.posts_id order by tc.create_time desc limit 9999 ) t group by t.oid
Done
추가 지식 포인트:mysql5.5와 mysql 5.7 버전의 차이점: 5.7+ 버전, 제한을 사용하지 않는 경우, 그룹 by will 최적화 주문 by