注解实现 条件、foreach
@Select("<script> SELECT * FROM (" +
"SELECT e.*, ROWNUM rn from ( "+
"select a.id as \"id\",a.nubmer_purchase as \"nubmerPurchase\",c.unit as \"unit\" "+
"from tin_materials_procurement_reco a "+
"left join tin_materials_procurement b on b.id = a.procurement_id "+
"where a.isdelete = 0 and a.nubmerPurchase - a.numberContract <> 0 " +
"<if test=' idArr != null and idArr.size() > 0 '> "+
"and a.id not in <foreach item='item' index='index' collection='idArr' open='(' separator=',' close=')'> #{item} </foreach> "+
"</if> "+
"<if test='name != null'> "+
"and b.code like '%${name}%' "+
"</if> "+
"ORDER BY a.CREATE_DATETIME DESC) e "+
"WHERE (#{pageNum} * #{pageSize}) >= ROWNUM) " +
"WHERE rn > (#{pageNum} - 1) * #{pageSize} </script> ")
foreach
| 参数 | 说明 |
|---|---|
| collection | 遍历的类型,(集合为list,数组为array,如果方法参数是对象的某个属性,而这个属性是list,或array类型,就可以写形参的名字) |
| open | 条件的开始 |
| close | 条件的结束 |
| item | 遍历集合时候定义的临时变量，存储当前遍历的每一个值 |
| separator | 多个值之间用逗号拼接 |
| #{id_} | 获取遍历的每一个值,与item定义的临时变量一致 |
| 原文链接 | blog.csdn.net/qq_38853322… |
mybatis plus 自定义XML生成的sql，是错的，rowid有两个，记录一下
SELECT *
FROM (SELECT TMP.*, ROWNUM ROW_ID
FROM (SELECT *
FROM (SELECT TMP_PAGE.*, ROWNUM ROW_ID
FROM (select f.ID
from A表 f
left join B表 b
on f.project_code = b.project_code
order by f.update_time asc) TMP_PAGE
WHERE ROWNUM <= ?)
WHERE ROW_ID > ?) TMP
WHERE ROWNUM <= 50)
WHERE ROW_ID > 0
