我的需求是查出每个球队中的助攻数最多的球员信息。
最开始使用配置的sql是:
<select id="selectMaxAssistListByTeamId" resultMap="BaseResultMap" parameterType="java.util.List" >
select
<include refid="Base_Column_List" />
from tab_player
where team_id in
<foreach collection="list" item="item" index="index" separator="," open="(" close=")">
#{item,jdbcType=VARCHAR}
</foreach>
Order By assist desc limit 1
</select>
这个SQL只能查出一条记录,显然不合逻辑。应该把order by 移进foreach,一起循环 :
<select id="selectMaxAssistListByTeamId" resultMap="BaseResultMap" parameterType="java.util.List" >
select
<include refid="Base_Column_List" />
from tab_player
where
<foreach collection="list" item="item" index="index" separator="," >
team_id = #{item,jdbcType=VARCHAR} Order By assist desc limit 1
</foreach>
</select>
还是不行,报错:...Exception:Undeclared variable:team_id.
百度无结果,都是用的in,显然不合我的要求,应该把in换掉。想了想,其实tab_player这个表里team_id
都是关联自tab_team这个表的team_id,用in的话不但是多此一举并且插叙速度还很慢。
应该继续改进第二个sql换个思维:
用union!把separator的逗号改成UNION,并且要用括号()把sql包起来,不然报错:Incorrect usage of UNION and ORDER BY.
<select id="selectMaxAssistListByTeamId" resultMap="BaseResultMap" parameterType="java.util.List" >
<foreach collection="list" item="item" index="index" separator="UNION" >
(select
<include refid="Base_Column_List" />
from tab_xsj_player
where
team_id = #{item,jdbcType=VARCHAR} Order By assist desc limit 1)
</foreach>
</select>
本方法用的是union,有用in能查出来的请献出你的想法大家共同学习!