Mysql 查询使用 Like 运算符并按关键字排序，在 Spring Boot api 中给出空记录

Question

我在存储库中编写的本机查询

如果我在下面的查询中删除按条件排序，它会给出一些记录，但我希望按某种顺序排列，所以我添加了按条件排序，然后它不只显示记录。但同样的带有 order by 条件的 mysql 查询在 mysql 工作台中给出了一些记录。

  @Query(value = "SELECT l.id AS id,l.first_name AS firstName,l.last_name AS lastName,"
  + "    l.email AS email,l.phone AS phone,l.place AS place,l.course_id AS courseId,"
  + "    c.name AS courseName,l.source AS source,l.enquiry_for AS enquiryFor,"
  + "    l.appointment_date AS appointmentDate,l.description AS description,"
  + "    l.discount AS discount,l.status AS status,l.assignee_id AS assigneeId,"
  + "    ase.first_name AS assigneeFirstName,ase.last_name AS assigneeLastName,"
  + "    l.assignor_id AS assignorId,asr.first_name AS assignorFirstName,"
  + "    asr.last_name AS assignorLastName,l.active AS active,l.created_date AS createdDate,"
  + "    l.updated_date AS updatedDate,(SELECT comments   FROM reviews where  created_date IN"
  + "    (SELECT MAX(created_date) from reviews where lead_id=l.id)) AS latestComment FROM "
  + "    leads l JOIN users AS ase ON l.assignee_id = ase.id JOIN users AS asr ON "
  + "   l.assignor_id = asr.id JOIN courses AS c ON l.course_id =c.id WHERE l.status!='Draft'"
  + "    AND (l.first_name LIKE '%:keyword%' OR l.last_name LIKE '%:keyword%' OR l.phone LIKE"
  + "   '%:keyword%') ORDER BY -l.appointment_date DESC,l.created_date ASC", nativeQuery=true)
  List<Leads> searchLeadsForAdmin(@Param("keyword") String searchKeyword);

我不知道我哪里出错了，下面的附图是 mysql 查询结果出现在工作台中的。

Answer 1

我找到了我的问题的答案。这是一个串联问题。在“l.phone LIKE”之后的查询的倒数第二行中，我在下一行中添加了值。

+ "    AND l.first_name LIKE %:keyword% OR l.last_name LIKE %:keyword% OR  "
+ "    l.phone LIKE %:keyword%  ORDER BY -l.appointment_date DESC,"
+ "    l.created_date ASC", nativeQuery = true)

上述查询有效。