mysql+mybatis 外连接拼接字段--gxy

比眉伴天荒 2022-06-03 10:50 221阅读 0赞

<select id="queryInfo" resultMap="BaseCustomerResultMap">
    
            select t.*, c.num,c.communicateTime from t_baseimportcustom t
            LEFT JOIN (
                SELECT
                    tt.baseId,
                    COUNT(tt.id) num,
                    tt.communicateTime
                FROM
                    t_commurecord tt
                GROUP BY
                    tt.baseId,
                    tt.communicateTime
                ORDER BY
                    tt.communicateTime
                LIMIT 1
            ) c ON c.baseId = t.id
            where t.factoryId = #{factoryId}
            <if test="customername != null">
                and t.customerName like #{customername}
                or t.phoneName like #{customername}
                or t.jobLevel like #{customername}
                or t.belongCompany like #{customername}
                or t.belongMarket like #{customername}
            </if>
          </select>

使用外连接新生成的非数据库字段组合。

java

package com.fan.springboot.entity;
    
    import java.util.Date;
    
    /** * 重要客户之基础信息 * @author GJY * * @version 创建时间：2017年12月11日 上午10:11:30 * */
    public class BaseCustomerMessage { 
        private Long id;
    
        private String customername;
    
        private String phonename;
    
        private String joblevel;
    
        private String hobby;
    
        private String belongmarket;
    
        private String belongcompany;
    
        private String birthday;
    
        private String borthdayhabit;
    
        private String homemate;
    
        private String selftaboo;
    
        private String createuserid;
    
        private String createusername;
    
        private String factoryid;
    
        private Date createdate;
    
        private Date updatedate;
    
        /**附加字段**/
        private Integer num;
    
        private Date lastRedTime;
    
        public Integer getNum() {
            return num;
        }
    
        public void setNum(Integer num) {
            this.num = num;
        }
    
        public Date getLastRedTime() {
            return lastRedTime;
        }
    
        public void setLastRedTime(Date lastRedTime) {
            this.lastRedTime = lastRedTime;
        }
    
        public Long getId() {
            return id;
        }
    
        public void setId(Long id) {
            this.id = id;
        }
    
        public String getCustomername() {
            return customername;
        }
    
        public void setCustomername(String customername) {
            this.customername = customername == null ? null : customername.trim();
        }
    
        public String getPhonename() {
            return phonename;
        }
    
        public void setPhonename(String phonename) {
            this.phonename = phonename == null ? null : phonename.trim();
        }
    
        public String getJoblevel() {
            return joblevel;
        }
    
        public void setJoblevel(String joblevel) {
            this.joblevel = joblevel == null ? null : joblevel.trim();
        }
    
        public String getHobby() {
            return hobby;
        }
    
        public void setHobby(String hobby) {
            this.hobby = hobby == null ? null : hobby.trim();
        }
    
        public String getBelongmarket() {
            return belongmarket;
        }
    
        public void setBelongmarket(String belongmarket) {
            this.belongmarket = belongmarket == null ? null : belongmarket.trim();
        }
    
        public String getBelongcompany() {
            return belongcompany;
        }
    
        public void setBelongcompany(String belongcompany) {
            this.belongcompany = belongcompany == null ? null : belongcompany.trim();
        }
    
        public String getBirthday() {
            return birthday;
        }
    
        public void setBirthday(String birthday) {
            this.birthday = birthday == null ? null : birthday.trim();
        }
    
        public String getBorthdayhabit() {
            return borthdayhabit;
        }
    
        public void setBorthdayhabit(String borthdayhabit) {
            this.borthdayhabit = borthdayhabit == null ? null : borthdayhabit.trim();
        }
    
        public String getHomemate() {
            return homemate;
        }
    
        public void setHomemate(String homemate) {
            this.homemate = homemate == null ? null : homemate.trim();
        }
    
        public String getSelftaboo() {
            return selftaboo;
        }
    
        public void setSelftaboo(String selftaboo) {
            this.selftaboo = selftaboo == null ? null : selftaboo.trim();
        }
    
        public String getCreateuserid() {
            return createuserid;
        }
    
        public void setCreateuserid(String createuserid) {
            this.createuserid = createuserid == null ? null : createuserid.trim();
        }
    
        public String getCreateusername() {
            return createusername;
        }
    
        public void setCreateusername(String createusername) {
            this.createusername = createusername == null ? null : createusername.trim();
        }
    
        public String getFactoryid() {
            return factoryid;
        }
    
        public void setFactoryid(String factoryid) {
            this.factoryid = factoryid == null ? null : factoryid.trim();
        }
    
        public Date getCreatedate() {
            return createdate;
        }
    
        public void setCreatedate(Date createdate) {
            this.createdate = createdate;
        }
    
        public Date getUpdatedate() {
            return updatedate;
        }
    
        public void setUpdatedate(Date updatedate) {
            this.updatedate = updatedate;
        }
    
        @Override
        public String toString() {
            return "BaseCustomerMessage [id=" + id + ", customername=" + customername + ", phonename=" + phonename
                    + ", joblevel=" + joblevel + ", hobby=" + hobby + ", belongmarket=" + belongmarket + ", belongcompany="
                    + belongcompany + ", birthday=" + birthday + ", borthdayhabit=" + borthdayhabit + ", homemate="
                    + homemate + ", selftaboo=" + selftaboo + ", createuserid=" + createuserid + ", createusername="
                    + createusername + ", factoryid=" + factoryid + ", createdate=" + createdate + ", updatedate="
                    + updatedate + "]";
        }
    
    
    
    }