MySQL 搜索（按相关性排序）

发布于 2024-08-25 11:22:35 字数 360 浏览 9 评论 0原文

任何人都可以帮助我如何按以下标准的相关性对行进行排序吗？

`tbluser`
- - - - - - -
First Name
Last Name

`tbleduc`
- - - - - - -
School
College
University

在搜索表单上，用户具有以下字段，

Name
School
College
University

其中学校学院和大学是可选的。

名称被分成2个单词（中间的其他单词被省略），第一个单词被视为第一个名字，最后一个单词被视为姓氏。

现在我想实现基于相关性的搜索。

感谢您的帮助:)

原文

Can any one help me how to sort rows by relevance for the following criterion ?

`tbluser`
- - - - - - -
First Name
Last Name

`tbleduc`
- - - - - - -
School
College
University

On the search form the user has following fields

Name
School
College
University

Where School College and University are Optional..

And Name is split into 2 words (other words in middle are omitted), first word is taken as first anme and last word as last name..

Now I would like to implement search based on relevance.

Thanks for the help :)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

执笔绘流年 2024-09-01 11:22:35

好的，我在 Postgres 上为你尝试了这个（我这里没有 MySQL，所以可能有点不同）：

select matches.id,
      (matches.tfirstname
       + matches.tlastname 
       + matches.tschool
       + matches.tcollege
       + matches.tuniversity) as total
from (
   select u.id,
          (case when u.firstname like '%a%' then 1 else 0 end) as tfirstname,
          (case when u.lastname like '%b%' then 1 else 0 end) as tlastname,
          sum(e2.nschool) as tschool,
          sum(e2.ncollege) as tcollege,
          sum(e2.nuniversity) as tuniversity
   from tbluser u left outer join (
      select e.usr,
             (case when e.school like '%c%' then 1 else 0 end) as nschool,
             (case when e.college like '%d%' then 1 else 0 end) as ncollege,
             (case when e.university like '%e%' then 1 else 0 end) as nuniversity
      from tbleduc e
   ) e2 on u.id=e2.usr
   group by u.id, u.firstname, u.lastname
) as matches

我使用这些 DDL 语句来创建表：

create table tbluser (
  id int primary key,
  firstname varchar(255),
  lastname varchar(255)
)


create table tbleduc (
  id int primary key,
  usr int references tbluser,
  school varchar(255),
  college varchar(255),
  university varchar(255)
)

还有一点示例数据：

insert into tbluser(id, firstname, lastname)
            values (1, 'Jason', 'Bourne');
insert into tbleduc(id, usr, school, college, university)
            values (1, 1, 'SomeSchool', 'SomeCollege', 'SomeUniversity');
insert into tbleduc(id, usr, school, college, university)
            values (2, 1, 'MoreSchool', 'MoreCollege', 'MoreUniversity');

查询可以简化有点，如果 tbluser 和 tbleduc 之间的关系是 1:1。

不要忘记将 %a%, %b, ... 替换为您的变量（我建议使用准备好的语句）。

我希望这个模板可以作为一个基本解决方案有所帮助 - 您可以根据需要调整它:-) 您还可以删除最外面的 select 语句，以获取各个结果的计数器。

Okay, I tried this for you on Postgres (I don't have MySQL here, so maybe it's a little bit different):

select matches.id,
      (matches.tfirstname
       + matches.tlastname 
       + matches.tschool
       + matches.tcollege
       + matches.tuniversity) as total
from (
   select u.id,
          (case when u.firstname like '%a%' then 1 else 0 end) as tfirstname,
          (case when u.lastname like '%b%' then 1 else 0 end) as tlastname,
          sum(e2.nschool) as tschool,
          sum(e2.ncollege) as tcollege,
          sum(e2.nuniversity) as tuniversity
   from tbluser u left outer join (
      select e.usr,
             (case when e.school like '%c%' then 1 else 0 end) as nschool,
             (case when e.college like '%d%' then 1 else 0 end) as ncollege,
             (case when e.university like '%e%' then 1 else 0 end) as nuniversity
      from tbleduc e
   ) e2 on u.id=e2.usr
   group by u.id, u.firstname, u.lastname
) as matches

I used these DDL statements to create the tables:

create table tbluser (
  id int primary key,
  firstname varchar(255),
  lastname varchar(255)
)


create table tbleduc (
  id int primary key,
  usr int references tbluser,
  school varchar(255),
  college varchar(255),
  university varchar(255)
)

And a little bit of example data:

insert into tbluser(id, firstname, lastname)
            values (1, 'Jason', 'Bourne');
insert into tbleduc(id, usr, school, college, university)
            values (1, 1, 'SomeSchool', 'SomeCollege', 'SomeUniversity');
insert into tbleduc(id, usr, school, college, university)
            values (2, 1, 'MoreSchool', 'MoreCollege', 'MoreUniversity');

The query can be simplified a bit, if the relationship between tbluser and tbleduc is 1:1.

Don't forget to replace the %a%, %b, ... with your variables (I recommend using a prepared statement).

I hope this template helps as a basic solution - you can tweak it as much as you like :-) You can also remove the outermost select statement, to get the counters of the individual results.

回复收藏 0 原文