java mybatis mysql多条件模糊查询怎么实现

Question

多表 多字段模糊查询 一个查询 匹配作者名或帖子名 或论坛名 这个改怎么实现 不使用es的情况下 sql怎么实现

Answer 1

SELECT
a.id AS "id",
a.forum_id AS "forumId",
a.title AS "tzTitle", //想要模糊查询的字段
a.content AS "content",
a.author_id AS "authorId",
a.create_by AS "createBy.id",
a.create_date AS "createDate",
a.update_by AS "updateBy.id",
a.update_date AS "updateDate",
a.del_flag AS "delFlag",
a.dznum AS "dznum",
a.plnum AS "plnum",
a.boutique AS "boutique",
u.NAME AS "authorName", //想要查询的字段
a.bq_id AS "bqId",
b.NAME AS "bqName",
t.title AS "talktitle", //想要模糊查询的字段
a.talk_id AS "talkId",
u.photo AS "photo",
f.title AS "title",
a.scnum AS "scnum",
f.tznum AS "tznum",
d.dz_num AS "zzdznum",
d.ft_num AS "zzftnum",
u.company_id AS "companyid",
a.posttype AS "postType"
FROM
lt_posts a
LEFT JOIN sys_user u ON u.id = a.author_id
LEFT JOIN lt_bq b ON b.id = a.bq_id
LEFT JOIN lt_talk t ON t.id = a.talk_id
LEFT JOIN lt_forum f ON f.id = a.forum_id
LEFT JOIN lt_author_dsg d ON d.author_id = a.author_id
WHERE
a.del_flag = 0
AND a.posttype = '2'
AND a.forum_id = “1"
And (a.title like concat(‘%’,#{keywords},’%’) or u.name like concat(‘%’,#{keywords},’%’) or t.title like concat(‘%’,#{keywords},’%’) )
ORDER BY
a.update_date DESC
LIMIT 5