昨天到今天都在搞多表查询,可能是我经验不足,搞得头大阿,越想越烦,早上在地铁里,终于想明白了,原来没那么复杂。 一开始想的是对的,后来发现查询出多条重复记录,就开始左思右想是不是sql写的不对,关系搞错了。其实这样写就可以搞定了。
<select id="getAdminByAdminOrFunction" parameterClass="java.util.Map" resultClass="photo.central.po.admin.Admin">
select DISTINCT a.* from admin_func_ref af
left join admin a on a.admin=af.admin
where 1 = 1
<dynamic prepend="and">
<isNotEmpty property="admin">
a.name like '%$name$%'
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isGreaterThan property="functionId" compareValue="0">
af.functionId = #functionId#
</isGreaterThan>
</dynamic>
</select>
这样查询,很神奇的地方就是一个distinct,看前人的代码受到的启发。
接下来Count出查询出记录的条数。
<select id="countQueryAdmin" resultClass="java.lang.Integer">
select count(DISTINCT a.admin) from admin_func_ref af
left join admin a on a.admin=af.admin
where 1 = 1
<dynamic prepend="and">
<isNotEmpty property="admin">
a.name like '%$name$%'
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isGreaterThan property="functionId" compareValue="0">
af.functionId = #functionId#
</isGreaterThan>
</dynamic>
</select>
唉 。。。。。。。。。。。。。。。。。
另外,在查询mysql手册 mysql 优化(4)里发现,当 LIMIT row_count 和 DISTINCT 一起联合起来时,MySQL在找到 row_count 条唯一记录后就不再搜索了。由此,此语句可以优化一下
<select id="getAdminByAdminOrFunction" parameterClass="java.util.Map" resultClass="photo.central.po.admin.Admin">
select DISTINCT a.* from admin_func_ref af
left join admin a on a.admin=af.admin
where 1 = 1
<dynamic prepend="and">
<isNotEmpty property="admin">
a.name like '%$name$%'
</isNotEmpty>
</dynamic>
<dynamic prepend="and">
<isGreaterThan property="functionId" compareValue="0">
af.functionId = #functionId#
</isGreaterThan>
</dynamic>
limit 1
</select>