qiuweilu opened a new issue, #28397:
URL: https://github.com/apache/shardingsphere/issues/28397
For complex SQL nested queries, how can I manually specify the table name to
avoid M*N queries.
part_cmnt_${bizFkid}
part_vote_${bizFkid}
part_vote_${bizFkid}
If I specify the table name programmatically, an error will be reported.
------------------------------------------------------------------------------------------------------------------------------------------------------
`<select id="searchCmntList"
parameterType="com.foxwish.dao.entity.ext.PartCmntExt"
resultMap="BaseResultMap">
SELECT out_cmnt.cmnt_id cmnt_id, out_cmnt.cmnt_pid cmnt_pid,
out_cmnt.cmnt_gid cmnt_gid, out_cmnt.cmnt_mark cmnt_mark, out_cmnt.cmnt_data
cmnt_data, out_cmnt.cmnt_num_son cmnt_num_son, out_cmnt.cmnt_num_grp
cmnt_num_grp, out_cmnt.for_uid for_uid,
out_cmnt.user_id user_id, out_cmnt.cmnt_ctime cmnt_ctime,
out_cmnt.part_fkid part_fkid, out_cmnt.vote_bind vote_bind,
out_cmnt.vote_label vote_label, IF(vote_bind IS NULL, 0,
out_cmnt.vote_num) vote_num
<if test="userId != null and userId > 0">, vote_user_tab.user_id
vote_uid</if>
FROM
(
SELECT cmnt_tab.*, count(cmnt_tab.cmnt_id) vote_num,
vote_bind_tab.bind_fkid vote_bind, vote_bind_tab.vote_label vote_label
FROM
(
<!-- 基础.评论数据 -->
SELECT base_cmnt.* FROM **part_cmnt_${bizFkid}** base_cmnt
WHERE base_cmnt.part_fkid = #{partFkid,jdbcType=BIGINT}
AND base_cmnt.cmnt_label = #{cmntLabel,jdbcType=VARCHAR}
AND base_cmnt.cmnt_mark = #{cmntMark,jdbcType=VARCHAR}
AND base_cmnt.cmnt_score <= #{cmntScore,jdbcType=INTEGER}
AND base_cmnt.cmnt_ctime >=
#{cmntCtime,jdbcType=TIMESTAMP}
AND base_cmnt.cmnt_del = 0
<if test="cmntGid == null or cmntGid == 0">
AND base_cmnt.cmnt_pid = 0
</if>
<if test="cmntGid != null and cmntGid > 0">
AND base_cmnt.cmnt_pid > 0
AND base_cmnt.cmnt_gid = #{cmntGid,jdbcType=BIGINT}
</if>
ORDER BY base_cmnt.cmnt_id DESC
LIMIT 2000
) cmnt_tab
<!-- 评论.点赞数据 -->
LEFT JOIN **part_vote_${bizFkid}** vote_bind_tab ON
vote_bind_tab.bind_fkid = cmnt_tab.cmnt_id
AND vote_bind_tab.part_fkid = #{partFkid,jdbcType=BIGINT}
AND vote_bind_tab.vote_label = #{voteLabel,jdbcType=VARCHAR}
AND vote_bind_tab.vote_mark = #{voteMark,jdbcType=VARCHAR}
AND vote_bind_tab.vote_del = 0
GROUP BY cmnt_tab.cmnt_id
) out_cmnt
<!-- 评论.用户点赞 -->
<if test="userId != null and userId > 0">
LEFT JOIN **part_vote_${bizFkid}** vote_user_tab ON
vote_user_tab.bind_fkid = out_cmnt.cmnt_id
AND vote_user_tab.user_id = #{userId,jdbcType=BIGINT}
AND vote_user_tab.part_fkid = #{partFkid,jdbcType=BIGINT}
AND vote_user_tab.vote_label = #{voteLabel,jdbcType=VARCHAR}
AND vote_user_tab.vote_mark = #{voteMark,jdbcType=VARCHAR}
AND vote_user_tab.vote_del = 0
GROUP BY out_cmnt.cmnt_id
</if>
</select>`
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail:
[email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
