sql - MySQL ORDER BY 关键字匹配

Question

我有一个这样的表:

mysql> select * from test;+-------------+| name        |+-------------+| one         || two         || three       || tic tac toe || tac toe tic |+-------------+5 rows in set (0.00 sec)

I would like to query it so that I get all the rows but with those rows matching a certain keyword first. This is what I got so far:

mysql> select * from test order by instr(name, 'tac') desc;+-------------+| name        |+-------------+| tic tac toe || tac toe tic || one         || two         || three       |+-------------+5 rows in set (0.01 sec)

The only problem with this is I would prefer to order the matching rows by how close to the beginning of the field the keyword occurs. Since instr() returns 0 for no match, non-matching rows come up first when I ORDER BY INSTR(name, 'tac') ASC. I haven't been able to figure out a way around this.

I need MySQL to order like this

1
2
3
4
0
0
0

或者我需要 instr() 返回 NULL 而不是 0。

Answer 1

您需要按 2 列排序，第一列指示是否匹配(使 0 到底部)

select *
from test
order by
    CASE WHEN instr(name, 'tac') = 0 then 1 else 0 end,
    instr(name, 'tac') desc;

关于使用 NULL 的注意事项，它们位于查询的顶部，因此将 0 转换为 null 对您不起作用。

select a
from (select 1 as a union all select null) b
order by a

结果

(NULL)
1