SELECT t.id, t.name, t.sex, t.head_img, t1.tel, t1.address AS userAddress, t2.province, t2.city FROM user t LEFTJOIN user_address t1 ON t1.user_id = t.id LEFTJOIN area t2 ON t2.id = t1.area_id WHERE ( t.id = ? AND t1.tel LIKE ? AND t.id > ?)
SELECT t.id, t.name, t.sex, t.head_img, t1.tel, t1.address AS userAddress, t2.province, t2.city FROM user t LEFTJOIN user_address t1 ON t1.user_id = t.id LEFTJOIN area t2 ON t2.id = t1.area_id LIMIT ?,?
voidtestJoin(){ List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, newMPJQueryWrapper<UserDO>() .selectAll(UserDO.class) .select("addr.tel", "addr.address", "a.province") .leftJoin("user_addressaddront.id= addr.user_id") .rightJoin("area a on addr.area_id = a.id") .like("addr.tel", "1") .le("a.province", "1")); } }
对应 sql
SELECT t.id, t.name, t.sex, t.head_img, addr.tel, addr.address, a.province FROM user t LEFTJOIN user_address addr on t.id = addr.user_id RIGHTJOIN area a on addr.area_id = a.id WHERE ( addr.tel LIKE ? AND a.province <= ?)
说明:
UserDTO.class 查询结果类(resultType)
selectAll(UserDO.class) 查询主表全部字段(主表实体类)默认主表别名 "t"
select() mp的select策略是覆盖,以最后一次为准,这里的策略是追加,可以一直select
主表字段可以用lambda,会自动添加表别名,主表别名默认是 t ,非主表字段必须带别名查询
SELECT t.id, t.name, t.sex, t.head_img, addr.tel, addr.address, a.province FROM user t LEFTJOIN user_address addr on t.id = addr.user_id RIGHTJOIN area a on addr.area_id = a.id LIMIT ?,?
voidtestJoin(){ List<UserDTO> list = userMapper.selectJoinList(UserDTO.class, newMPJQueryWrapper<UserDO>() .selectAll(UserDO.class) .select("addr.tel", "addr.address") //行列转换 .select("CASEt.sexWHEN '男' THEN '1' ELSE '0' ENDASsex") //求和函数 .select("sum(a.province) ASprovince") //自定义数据集 .leftJoin("(select * fromuser_address) addront.id= addr.user_id") .rightJoin("area a on addr.area_id = a.id") .like("addr.tel", "1") .le("a.province", "1") .orderByDesc("addr.id")); } }
对应sql
SELECT t.id, t.name, t.sex, t.head_img, addr.tel, addr.address, CASE t.sex WHEN'男'THEN'1'ELSE'0'ENDAS sex, sum(a.province) AS province FROM user t LEFTJOIN (select * from user_address) addr on t.id = addr.user_id RIGHTJOIN area a on addr.area_id = a.id WHERE ( addr.tel LIKE ? AND a.province <= ?) ORDERBY addr.id DESC