mysql根据权重排序

MySQL是一种非常流行的关系型数据库管理系统，可以被用于存储和处理大量的数据。其中一个常见的需求是对数据进行排序，在MySQL中可以使用ORDER BY语句对数据进行排序，这个语句提供了对数据按照不同的列或者表达式进行排序的能力。

除了默认的排序方式外，我们经常需要根据给定的条件进行排序。例如，如果我们想按照某个字段的权重进行排序，我们可以使用CASE WHEN语句来定义每种情况下的权重值。假设我们的数据表包含一个score字段，取值为0到100，我们可以实现以下的排序：

SELECT name, score
FROM student
ORDER BY
CASE
WHEN score >= 90 THEN 4
WHEN score >= 60 THEN 3
ELSE 2
END DESC,
score DESC;

在上面的代码中，我们首先使用CASE WHEN语句来定义每种分数情况下的权重值，得到一个临时的排序字段。然后我们对这个字段进行降序排列，以保证权重高的记录排在前面。如果存在相同的权重值，我们再按照分数降序排列，以保证分数高的记录排在前面。

另外一种常见的情况是根据多个字段排序。例如，我们有一个包含年龄和工资两个字段的员工表，我们想先按照年龄升序排序，如果年龄相同，则按照工资降序排序，可以使用以下的排序方式：

SELECT name, age, salary
FROM employee
ORDER BY age ASC, salary DESC;

在上面的代码中，我们首先按照年龄升序排列，然后在年龄相同的情况下按照工资降序排列。这个语句提供了多个排序条件的排序的能力。

总之，MySQL提供了非常灵活的排序功能，可以满足各种排序需求。在进行排序时，我们可以使用CASE WHEN语句生成临时的排序字段，也可以按照多个条件进行排序，以得到我们想要的结果。

原文：https://www.yzktw.com.cn/post/1020112.html

 

一个复杂的示例

先判断标题是否完全匹配，匹配则返回，否则再判断内容是否完全匹配，匹配则返回。标题和内容都没有完全匹配，则对分词进行权重相加。

这里有个设计的小技巧，内容完全匹配50分，标题中如果有3个以上分词匹配，就会超过50分，强于单个完全匹配的内容。

内容分词匹配6分，分词中如果有4个以上匹配就会超过20分，强于单个分词匹配的标题。

这里为了节约资源，分词在标题中有了就不再查内容了，当然你也可以都拆分开来，标题中有了也要再查内容，然后相加总分。

SELECT *,
    (
        CASE
            WHEN title LIKE '%读书为什么不快乐%' THEN 100
			WHEN content LIKE '%读书为什么不快乐%' THEN 50
            ELSE 
            CASE
                WHEN title LIKE '%读书%' THEN 20
                WHEN content LIKE '%读书%' THEN 6
                ELSE 0
            END +
            CASE
                WHEN title LIKE '%为什么%' THEN 20
                WHEN content LIKE '%为什么%' THEN 6
                ELSE 0
            END +
            CASE
                WHEN title LIKE '%快乐%' THEN 20
                WHEN content LIKE '%快乐%' THEN 6
                ELSE 0
            END 
        END
        ) AS weight
FROM articles
WHERE (title LIKE '%读书%' OR title LIKE '%为什么%' OR title LIKE '%快乐%')
   OR (content LIKE '%读书%' OR content LIKE '%为什么%' OR content LIKE '%快乐%')
ORDER BY weight DESC;

如果要计算每个词的分数，并按总分排序，可以这样写

SELECT *,
    (CASE WHEN title LIKE '%读书为什么不快乐%' THEN 100 ELSE 0 END +
     CASE WHEN content LIKE '%读书为什么不快乐%' THEN 50 ELSE 0 END +
     CASE WHEN title LIKE '%读书%' THEN 20 ELSE 0 END +
     CASE WHEN content LIKE '%读乐%' THEN 7 ELSE 0 END +
     CASE WHEN title LIKE '%为什么%' THEN 20 ELSE 0 END +
     CASE WHEN content LIKE '%为什么%' THEN 7 ELSE 0 END +
     CASE WHEN title LIKE '%快乐%' THEN 20 ELSE 0 END +
     CASE WHEN content LIKE '%快乐%' THEN 7 ELSE 0 END) AS weight
FROM articles
WHERE (title LIKE '%读书%' OR title LIKE '%为什么%' OR title LIKE '%快乐%')
   OR (content LIKE '%读书%' OR content LIKE '%为什么%' OR content LIKE '%快乐%')
ORDER BY weight DESC;

 

如果要看各个分数的详情，可以这样写

SELECT *,
    CASE WHEN title LIKE '%读书为什么不快乐%' THEN 100 ELSE 0 END AS score1,   
	CASE WHEN content LIKE '%读书为什么不快乐%' THEN 50 ELSE 0 END AS score2,
    CASE WHEN title LIKE '%读书%' THEN 20 ELSE 0 END AS score3,
    CASE WHEN content LIKE '%读乐%' THEN 7 ELSE 0 END AS score4,
    CASE WHEN title LIKE '%为什么%' THEN 20 ELSE 0 END AS score5,
    CASE WHEN content LIKE '%为什么%' THEN 7 ELSE 0 END AS score6,
    CASE WHEN title LIKE '%快乐%' THEN 20 ELSE 0 END AS score7,
    CASE WHEN content LIKE '%快乐%' THEN 7 ELSE 0 END AS score8
FROM articles
WHERE (title LIKE '%读书%' OR title LIKE '%为什么%' OR title LIKE '%快乐%')
   OR (content LIKE '%读书%' OR content LIKE '%为什么%' OR content LIKE '%快乐%')
ORDER BY (score1 + score2 + score3 + score4 + score5 + score6 + score7 + score8) DESC;

不区分大小写

在 MySQL 或 MariaDB 中，可以使用 COLLATE 关键字来指定大小写不敏感的字符集（collation）。

SELECT *
FROM table_name
WHERE column_name LIKE '%abc%' COLLATE utf8_general_ci;

在 SQLite 中，要实现大小写不敏感的搜索，可以通过在查询中使用 `COLLATE NOCASE` 来实现。SQLite 不像某些其他数据库系统那样具有严格的字符集和 collation 设置，因此可以直接使用 `COLLATE NOCASE` 来进行大小写不敏感的搜索。

示例查询：

SELECT *
FROM table_name
WHERE column_name LIKE '%abc%' COLLATE NOCASE;

在上面的例子中，`COLLATE NOCASE` 指示 SQLite 在执行 `LIKE` 操作时不区分大小写。这种方式简单直接，适用于 SQLite 数据库中的文本搜索需求。