mysql语句
SELECT
t1.number merchantNumber,
t1.district_number districtNumber,
t1.fraction,
t1.latitude,
t1.longitude,
t1.merchant_name merchantName,
t1.user_id userId,
t3.district_name districtName,
t4.avatar_url avatarUrl,
t4.nickname
FROM
ec_merchant t1
LEFT JOIN sys_user t2 ON t1.user_id = t2.user_id
LEFT JOIN ec_district t3 ON t1.district_number = t3.number
LEFT JOIN ec_we_chat_user t4 ON t1.user_id = t4.personnel_id
WHERE
t1.del_flag = 0
AND t4.personnel_id IS NOT NULL
AND t3.number = 'aWj2gj5NfpFkxx57ZFElp'
AND t1.user_id = '29'
AND t1.fraction BETWEEN 0 AND 40;
GROUP BY t1.fraction DESC
service.java
IPage<Map<String, Object>> findWarningMerchantList(
@Param("page") Page<Object> page,
@Param("ew") QueryWrapper<Object> wrapper);
serviceImpl.java
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import org.apache.commons.lang3.StringUtils;
@Override
public AjaxResult findWarningMerchantList(JSONObject data) {
String userId = data.getString("userId");
String districtNumber = data.getString("districtNumber");
ArrayList<Integer> fraction = data.getObject("fraction", ArrayList.class);
Integer startIndex = data.getInteger("startIndex");
Integer pageSize = data.getInteger("pageSize");
Page<Object> page = new Page<>(startIndex, pageSize);
QueryWrapper<Object> wrapper = new QueryWrapper<>();
wrapper.eq( "t1.del_flag", 0);
wrapper.isNotNull( "t4.personnel_id");
wrapper.eq(StringUtils.isNotBlank(districtNumber), "t3.number", districtNumber);
wrapper.eq(StringUtils.isNotBlank(userId), "t1.user_id", userId);
if (fraction.size() > 0) {
wrapper.between("t1.fraction", fraction.get(0), fraction.get(1));
}
wrapper.orderByDesc("t1.fraction");
IPage<Map<String, Object>> result = baseMapper.findWarningMerchantList(page, wrapper);
return AjaxResult.success(result);
}
mapper.xml
<select id="findWarningMerchantList" resultType="java.util.Map">
SELECT
t1.number merchantNumber,
t1.district_number districtNumber,
t1.fraction,
t1.latitude,
t1.longitude,
t1.merchant_name merchantName,
t1.user_id userId,
t3.district_name districtName,
t4.avatar_url avatarUrl,
t4.nickname
FROM
ec_merchant t1
LEFT JOIN sys_user t2 ON t1.user_id = t2.user_id
LEFT JOIN ec_district t3 ON t1.district_number = t3.number
LEFT JOIN ec_we_chat_user t4 ON t1.user_id = t4.personnel_id
${ew.customSqlSegment}
</select>