用途:
SELECT A.*,
GROUP_CONCAT(DISTINCT w.tag_word ORDER BY w.tag_word ASC SEPARATOR ',') AS tags,
COUNT(DISTINCT w.tag_word) +
(CASE WHEN `description` LIKE '%hotel%' THEN 1 ELSE 0 END) +
(CASE WHEN `description` LIKE '%london%' THEN 1 ELSE 0 END) +
(CASE WHEN `description` LIKE '%lazy%' THEN 1 ELSE 0 END) +
(CASE WHEN `description` LIKE '%dog%' THEN 1 ELSE 0 END) +
(CASE WHEN `title` LIKE '%hotel%' THEN 1 ELSE 0 END) +
(CASE WHEN `title` LIKE '%london%' THEN 1 ELSE 0 END) +
(CASE WHEN `title` LIKE '%lazy%' THEN 1 ELSE 0 END) +
(CASE WHEN `title` LIKE '%dog%' THEN 1 ELSE 0 END) AS relevance
FROM tbl_articles A
JOIN tag_index I ON A.article_id = I.tag_target_id
JOIN tag_word W ON I.tag_word_id = W.tag_word_id
WHERE I.tag_type_id = 1
AND w.tag_word IN ('hotel', 'london', 'lazy', 'dog')
GROUP BY a.article_id, a.title, a.description, a.content
ORDER BY relevance DESC