mysql - 如何比较同一个表的行并将另一个表的权重应用于每一列

Question

我正在尝试在 MySQL 中构建一种“个人”匹配数据库。

• 我需要将 Members 表中的特定人员与同一表中的所有其他人进行比较。

• 表格中的每一行(人)都有许多列及其信息(年龄、位置、宗教等)。

• 我需要查询来引用一个表，该表包含我对每一列的“权重”。换句话说，我想说“位置在 75 岁时很重要，年龄范围在 100 岁时 super 重要，宗教在 10 岁时不重要”。

成员表

+----+-------+----------+----------+-----+----------+
| ID | Name  | Location | Religion | Age | AgeRange |
+----+-------+----------+----------+-----+----------+
| 1  | Joe   | LA       | Athiest  | 40  | 30-40    |
+----+-------+----------+----------+-----+----------+
| 2  | Mary  | LA       | Agnostic | 35  | 35-45    |
+----+-------+----------+----------+-----+----------+
| 3  | Karen | NYC      | Athiest  | 45  | 30-35    |
+----+-------+----------+----------+-----+----------+
| 4  | Lisa  | LA       | Hindu    | 30  | 45-55    |
+----+-------+----------+----------+-----+----------+

权重表(一个参数有多重要)

+----+-----+----------+----------+
| ID | Age | Location | Religion |
+----+-----+----------+----------+
| 1  | 100 | 75       | 10       |
+----+-----+----------+----------+

在过去的 2 天里，我尝试了很多事情，但我尝试使用的最新查询是这个，这显然没有多大用处。它也没有指定将这些记录与哪些“人”进行比较。

SELECT  a.first_name,
        g.name, 
        a.age* g.age+
        a.location* g.location+
        a.religion * g.mwReligion AS metric
    FROM members a, weight g  
    ORDER BY metric DESC;

我的预期输出是这样的:

乔匹配:

玛丽 - 分数 = 285
(100 因为她在他的年龄范围内 + 100 因为他在她的年龄范围内 + 位置 75 + 宗教 10)

丽莎 - 分数 = 175(100 因为她在他的 AgeRange + 75 位置)

凯伦 - 分数 = 10(仅宗教匹配)

Answer 1

我假设 min_age 和 max_age 列是分开的(而不是 AgeRange)，包含在内，并且属于 INT数据类型。您需要的查询应如下所示:

select 
  x.id,
  x.name,
  x.ma as match_age,
  x.ml as match_location,
  x.mr as match_religion,
  x.ma * w.age + x.ml * w.location + x.mr * w.religion as total_score
from (
  select
    o.id,
    o.name,
    case when o.age between p.min_age and p.max_age then 1 else 0 end as ma,
    case when o.location = p.location then 1 else 0 end as ml,
    case when o.religion = p.religion then 1 else 0 end as mr
  from (select * from members where id = 1) p -- selects Joe
  cross join (select * from members where id <> 1) o -- select other members
) x
cross join weights w