1. <legend id='RXMqB'><style id='RXMqB'><dir id='RXMqB'><q id='RXMqB'></q></dir></style></legend>
      <bdo id='RXMqB'></bdo><ul id='RXMqB'></ul>
  2. <small id='RXMqB'></small><noframes id='RXMqB'>

    <tfoot id='RXMqB'></tfoot>

    1. <i id='RXMqB'><tr id='RXMqB'><dt id='RXMqB'><q id='RXMqB'><span id='RXMqB'><b id='RXMqB'><form id='RXMqB'><ins id='RXMqB'></ins><ul id='RXMqB'></ul><sub id='RXMqB'></sub></form><legend id='RXMqB'></legend><bdo id='RXMqB'><pre id='RXMqB'><center id='RXMqB'></center></pre></bdo></b><th id='RXMqB'></th></span></q></dt></tr></i><div id='RXMqB'><tfoot id='RXMqB'></tfoot><dl id='RXMqB'><fieldset id='RXMqB'></fieldset></dl></div>

      MySQL - 如何按相关性排序?INNODB 表

      MySQL - How to ORDER BY RELEVANCE? INNODB Table(MySQL - 如何按相关性排序?INNODB 表)
      • <bdo id='SyVIx'></bdo><ul id='SyVIx'></ul>
          <legend id='SyVIx'><style id='SyVIx'><dir id='SyVIx'><q id='SyVIx'></q></dir></style></legend>

            • <small id='SyVIx'></small><noframes id='SyVIx'>

              <tfoot id='SyVIx'></tfoot>
              <i id='SyVIx'><tr id='SyVIx'><dt id='SyVIx'><q id='SyVIx'><span id='SyVIx'><b id='SyVIx'><form id='SyVIx'><ins id='SyVIx'></ins><ul id='SyVIx'></ul><sub id='SyVIx'></sub></form><legend id='SyVIx'></legend><bdo id='SyVIx'><pre id='SyVIx'><center id='SyVIx'></center></pre></bdo></b><th id='SyVIx'></th></span></q></dt></tr></i><div id='SyVIx'><tfoot id='SyVIx'></tfoot><dl id='SyVIx'><fieldset id='SyVIx'></fieldset></dl></div>
                  <tbody id='SyVIx'></tbody>
                本文介绍了MySQL - 如何按相关性排序?INNODB 表的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

                问题描述

                我在一个名为cards"的 INNODB 表中有大约 20,000 行,所以 FULLTEXT 不是一个选项.

                I've got about 20,000 rows in an INNODB table called 'cards', so FULLTEXT is not an option.

                请考虑这张表:

                id     |     name     |     description
                ----------------------------------------------------------
                1        John Smith       Just some dude
                2        Ted Johnson      Another dude
                3        Johnathan Todd   This guy too
                4        Susan Smith      Her too
                5        Sam John Bond    And him
                6        John Smith       Same guy as num 1, another record
                7        John Adams       Last guy, promise
                

                因此,假设用户搜索John",我希望结果集的顺序为:

                So, say the user searches for 'John', I want the result set to be in the order of:

                7        John Adams
                6        John Smith
                3        Johnathan Todd
                5        Sam John Bond
                2        Ted Johnson
                

                请注意,我们只提取了一次John Smith",我们选取了他最近的条目.由于我的数据,所有的名字都是同一个人的,不用担心有两个不同的人叫约翰史密斯.想法?如果我能澄清任何事情,请告诉我.

                Please note that we've only pulled 'John Smith' once, we took his most recent entry. Due to my data, all names are for the same exact person, no need to worry about 2 different guys named John Smith. Ideas? Let me know if I can clarify anything.

                推荐答案

                version 1:

                SELECT max(id) id, name
                  FROM cards
                 WHERE name like '%John%'
                 GROUP BY name
                 ORDER BY CASE WHEN name like 'John %' THEN 0
                               WHEN name like 'John%' THEN 1
                               WHEN name like '% John%' THEN 2
                               ELSE 3
                          END, name
                

                版本 2:

                SELECT max(id) id, name
                  FROM cards
                 WHERE name like '%John%'
                 GROUP BY name
                 ORDER BY CASE WHEN name like 'John%' THEN 0
                               WHEN name like '% %John% %' THEN 1
                               WHEN name like '%John' THEN 2
                               ELSE 3
                          END, name
                

                这篇关于MySQL - 如何按相关性排序?INNODB 表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!

                相关文档推荐

                Set the variable result, from query(设置变量结果,来自查询)
                What is dynamic SQL?(什么是动态 SQL?)
                Mysql - How to quit/exit from stored procedure(Mysql - 如何退出/退出存储过程)
                Does MySQL have time-based triggers?(MySQL 有基于时间的触发器吗?)
                is it possible to call a sql script from a stored procedure in another sql script?(是否可以从另一个 sql 脚本中的存储过程调用 sql 脚本?)
                Procedure to loop through comma separated string is not working(遍历逗号分隔字符串的过程不起作用)
                <tfoot id='UeiSy'></tfoot>
                  <tbody id='UeiSy'></tbody>

                  • <bdo id='UeiSy'></bdo><ul id='UeiSy'></ul>
                  • <legend id='UeiSy'><style id='UeiSy'><dir id='UeiSy'><q id='UeiSy'></q></dir></style></legend>

                      <i id='UeiSy'><tr id='UeiSy'><dt id='UeiSy'><q id='UeiSy'><span id='UeiSy'><b id='UeiSy'><form id='UeiSy'><ins id='UeiSy'></ins><ul id='UeiSy'></ul><sub id='UeiSy'></sub></form><legend id='UeiSy'></legend><bdo id='UeiSy'><pre id='UeiSy'><center id='UeiSy'></center></pre></bdo></b><th id='UeiSy'></th></span></q></dt></tr></i><div id='UeiSy'><tfoot id='UeiSy'></tfoot><dl id='UeiSy'><fieldset id='UeiSy'></fieldset></dl></div>
                        • <small id='UeiSy'></small><noframes id='UeiSy'>