我希望有人能帮助我。我正在尝试在WordPress中进行自定义SQL查询，并按照预定义的优先级对结果进行排序。
示例搜索字符串为"欧盟指令"，查询需要查看帖子的标题和名为"intro"的自定义字段（文本字段）。
我需要排序，将标题或介绍以某种方式匹配完整字符串的结果优先于搜索字符串中仅包含2个单词中1个的结果。
查询有效，但排序无效。仅与2个单词中的1个匹配的结果将在"intro"字段中包含完整字符串"EU Directive"的结果之前提取。
我原以为下面的查询会正确设置订单，但事实并非如此。
有人能看到明显的错误吗？现阶段如有任何帮助，我们将不胜感激。
SELECT * FROM wp_posts
INNER JOIN wp_postmeta ON (wp_posts.ID = wp_postmeta.post_id) INNER JOIN wp_postmeta AS mt1 ON (wp_posts.ID = mt1.post_id)
WHERE 1=1 AND ( (
(wp_posts.post_title LIKE 'EU Directive %') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive %')) )
OR ( (wp_posts.post_title LIKE 'EU Directive%') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive%')) )
OR ( (wp_posts.post_title LIKE '% EU Directive %') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive %')) )
OR ( (wp_posts.post_title LIKE '% EU Directive,%') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive,%')) )
OR ( (wp_posts.post_title LIKE '% EU Directive.%') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive.%')) )
OR ( (wp_posts.post_title LIKE '% EU Directive%') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive%')) )
OR ( (wp_posts.post_title LIKE 'EU %') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU %')) )
OR ( (wp_posts.post_title LIKE '% EU %') OR ((wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU %')) )
)
AND wp_posts.post_type = 'documents' AND (wp_posts.post_status = 'publish') GROUP BY wp_posts.ID
ORDER BY
(CASE
WHEN wp_posts.post_title LIKE 'EU Directive %' THEN 1
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive %') THEN 2
WHEN wp_posts.post_title LIKE 'EU Directive%' THEN 3
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE 'EU Directive%') THEN 4
WHEN wp_posts.post_title LIKE '% EU Directive %' THEN 5
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive %') THEN 6
WHEN wp_posts.post_title LIKE '% EU Directive,%' THEN 7
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive,%') THEN 8
WHEN wp_posts.post_title LIKE '% EU Directive.%' THEN 9
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive.%') THEN 10
WHEN wp_posts.post_title LIKE '% EU Directive%' THEN 11
WHEN (wp_postmeta.meta_key = 'intro' AND CAST(wp_postmeta.meta_value AS CHAR) LIKE '% EU Directive%') THEN 12
ELSE 13
END),
wp_posts.post_date DESC LIMIT 0, 10
如果我理解正确，在CASE语句中，您将尝试根据满足的条件输出排序值。问题是，从CASE语句中可以得到一个数字：假设它是1。在这种情况下，您只需要执行ORDER BY 1，意思是"通过第一列"。您应该将该CASE用作SELECT中的字段，然后将字段的名称放入ORDER BY子句中。类似这样的东西：
SELECT * FROM
(SELECT *, (CASE WHEN... THEN ...) AS position FROM wp_posts ...) data
ORDER BY data.position, data.post_date DESC LIMIT 0, 10