我有一个查询：

SELECT *
FROM Items
WHERE column LIKE '%foo%'
   OR column LIKE '%bar%'

如何订购结果？

假设我有与" foo"匹配的行和与" bar"匹配的行，但我也有一个具有" foobar"的行。

如何排序返回的行，以便第一个结果是与更多LIKE匹配的结果？

解决方案

回答

哪个DBMS？

例如，可以通过CTE或者Union来完成，但是如果使用的是MySQL，则可以将其遗忘。

回答

RDBMS支持的大小写或者这种条件构造是一种实现方法

select *, case when col like '%foo%' and col like '%bar%' then 2 end 
else 1 end as ordcol 
from items 
where col like '%foo%' or col like '%bar%' order by ordcol

回答

试试下面的代码：

SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%'
order by (select count(*) from items i where i.column= item.column) DESC

如果我们不关心详细信息，也可以按"列"和"计数(*)"再按"订单"进行分组。

回答

我们可能想尝试一下：

SELECT *
FROM Items
WHERE column LIKE '%foo%' OR column LIKE '%bar%'
ORDER BY CASE WHEN column LIKE '%foo%' AND column LIKE '%bar%' THEN 1 ELSE 0 END DESC

注意：这是经过干燥编码的，可能不太便于携带。

回答

我们可以使用UNION：

SELECT * FROM Items WHERE column LIKE '%foo%' AND column LIKE '%bar%'
UNION
SELECT * FROM Items WHERE column LIKE '%foo%' AND NOT (column LIKE '%bar%')
UNION
SELECT * FROM Items WHERE column LIKE '%bar%' AND NOT (column LIKE '%foo%');

但是，这可能会降低性能。更糟糕的是，我猜想我们要使用它来构建一个搜索引擎，该搜索引擎首先给出最有意义的结果，然后单词的数量不限于2.

在这种情况下，我们可以创建一个"分数"列，其中包含匹配数。像这样的东西：

SELECT
    *,
    (IF(column LIKE '%bar%', 1, 0) + IF(column LIKE '%foo%', 1, 0)) AS score
FROM Items
WHERE column LIKE '%foo%' OR column LIKE '%bar%'
ORDER BY score DESC;

我的SQL有点生疏，但至少在MySQL 5.0中应该可以实现。另请参见有关IF功能的手册：
http://dev.mysql.com/doc/refman/5.0/zh-CN/control-flow-functions.html

回答

SELECT * FROM Items WHERE column LIKE '%foo%' OR column LIKE '%bar%' 
ORDER BY 
(IF(column LIKE '%foo%',1,0) + IF(column LIKE '%bar%',1,0)) 
DESC

if的语法是

IF(条件，true_value，false_value)

回答

2个查询：

SELECT * FROM Items WHERE column LIKE '%foo%' AND column LIKE '%bar%';

SELECT * FROM Items WHERE (column LIKE '%foo%' AND column NOT LIKE '%bar%') OR (column NOT LIKE '%foo%' AND LIKE '%bar%')

(SQL中没有XOR)

回答

并非所有的RDBMS都支持IF(或者Oracle中的DECODE)语句。如果不是，则可以使用子查询来定义表" a"，并搜索所有员工的名为JO SMITH或者组合的名称。

SELECT 
 a.employee_id,
 a.surname,
 sum(a.counter)
FROM

 (SELECT
   employee_id,
   surname,
   1 as counter
  FROM
   MyTable
  WHERE
   surname like '%SMITH%'

  UNION ALL

  SELECT
   employee_id,
   surname,
   1 as counter
  FROM
   MyTable
  WHERE
   surname like '%JO%'
   ) a

GROUP BY 
 a.employee_id,
 a.surname
ORDER BY 3,1,2

确保使用UNION ALL，否则将无法使用。我们也可以使用UPPER()使搜索不区分大小写。

回答

由于当前正在编写查询，因此WHERE子句将不会为我们提供任何可用于对结果进行排序的信息。我喜欢布莱恩的主意；添加一个常量列，然后对查询进行UNION，我们甚至可以将所有结果集中在一个结果集中。例如：

SELECT 1 as rank, * FROM Items WHERE column LIKE '%foo%' AND column LIKE '%bar%'
UNION
SELECT 2 as rank, * FROM Items WHERE column LIKE '%foo%' AND column NOT LIKE '%bar%'
UNION
SELECT 2 as rank, * FROM Items WHERE column LIKE '%bar%' AND column NOT LIKE '%foo%'
ORDER BY rank

但是，这只会给我们这样的信息：

• 匹配foo和match bar的所有行的无序集合
• 然后是所有与foo或者bar匹配的行(无序集合)，但不能同时匹配两者(尽管我们可以在最后一个SELECT语句中使用不同的常量将其分成两个单独的组)。

这可能只是我们要查找的内容，但是它不会告诉我们哪些行与foo匹配了3次，或者没有将它们排在仅包含一个foo实例的行之前。同样，所有那些喜欢的人可能会变得昂贵。如果我们真正想做的是根据相关性对结果进行排序(无论我们如何定义)，那么最好使用全文索引。如果我们使用的是MS SQL Server，则它具有内置的服务来执行此操作，并且还有第三方产品也可以执行此操作。

编辑：在查看了所有其他答案之后(我刚开始时只有两个答案时，我显然必须学会更快地思考;-))显然，有几种方法可以解决此问题，具体取决于确切的内容我们正在尝试完成。我建议我们根据解决方案在系统上的性能来测试和比较解决方案。我不是性能/调优专家，但是函数会减慢速度，尤其是在对函数结果进行排序时。 LIKE运算符也不一定很灵巧。作为开发人员，使用" IF"和" CASE"等熟悉的结构似乎很自然，但是使用更多基于集合的方法的查询通常在RDMS中具有更好的性能。同样，YMMV，因此最好测试一下我们是否完全关心性能。

回答

SELECT * FROM Items
WHERE col LIKE '%foo%'
    OR col LIKE '%bar%'
ORDER BY CASE WHEN col LIKE '%foo%' THEN 1
                WHEN col LIKE '%bar%' THEN 2
            END