单张表通过1个字段清理数据
SELECT
*
FROM
fl_mall_member_setting
WHERE
M_ID IN (
SELECT
M_ID
FROM
fl_mall_member_setting
GROUP BY
M_ID
HAVING
count(M_ID) > 1
)
单张表通过2个字段清理数据
select *
from fl_mall_member_friends a
where exists (select 1
from (select M_ID, F_ID
from fl_mall_member_friends
group by M_ID, F_ID
having count(*) > 1) s
where s.M_ID = a.M_ID
and s.F_ID = a.F_ID)
/**
* @describe 查询好友列表带分页方法
* @author chaiyachun
* @returnType PageBean<SocialMember>
* @createTime 2018年5月30日 下午3:21:28
*/
HashMap<String,Object> paramMap = new HashMap<String,Object>();
paramMap.put("list", fids);
paramMap.put("startId", startId);
PageBean<SocialMember> bean = new PageBean<SocialMember>(socialMemberAndFriendDao.searchSocialFriendByPage(paramMap));
return bean;
/**
* @describe 查询社交会员好友列表带分页
* @author chaiyachun
* @returnType List<SocialMember>
* @createTime 2018年6月1日 下午2:23:00
*/
public List<SocialMember> searchSocialFriendByPage(Map<String,Object> map);
<!-- 查询社交好友列表带分页的方法 -->
<select id="searchSocialFriendByPage" resultMap="socialMemberResultMap">
SELECT
<include refid="socialMemberAll"/>
FROM FL_SOCIAL_MEMBER
WHERE
M_ID IN
<foreach item="fIds" collection="list" separator="," open="(" close=")" index="">
#{fIds}
</foreach>
AND M_ID >= #{startId}
</select>
******************************mysql 分页优化*****************************************************************
select * from product limit 866613, 20 37.44秒 原始方式
SELECT * FROM product WHERE ID > =(select id from product limit 866613, 1) limit 20
查询时间为0.2秒,简直是一个质的飞跃啊,哈哈
SELECT * FROM product a JOIN (select id from product limit 866613, 20) b ON a.ID = b.id