mysql - MySQL 根据偏好匹配两个用户

Question

好吧，我需要完成的有点复杂，但我会尽力解释。所以我有一张这样的桌子：

编号 || 颜色 || 红OK || 蓝色OK || 黄色OK || 绿色OK || 橙色OK

1-||----1---||----1-----||-----1-----||-------1--- ---||-----0------||------1------

2-||----3---||----1-----||-----0-----||-------1--- ---||------1------||------1------

3-||----1---||----0-----||-----0-----||-------0--- ---||-----0------||------1------

4-||----5---||----1-----||-----1-----||-------0--- ---||------1------||------1------

5-||----2---||----0-----||-----0-----||-------1--- ---||-----0------||------1------

在颜色字段中，数字 1 - 5 代表用户最喜欢的颜色（1 代表红色，2 代表蓝色，3 代表黄色，4 代表绿色，5 代表橙色）。redOK、blueOk、yellowOK、greenOK和orangeOK字段表示用户是否愿意与某个喜欢的颜色的人匹配。在这些字段中，1 表示是，0 表示否。例如，id=1 的用户最喜欢的颜色是红色，因为他们的“颜色”字段是 1，他们愿意与喜欢的颜色是红色、蓝色、黄色或橙色但不是绿色的人匹配，因为“greenOK”字段为 0。所以我需要做的是创建一个存储过程，该过程返回一个用户的颜色和颜色偏好与特定用户的颜色和颜色偏好相匹配的查询。因此，例如，具有 id' 的用户 s 1 和 2 将是匹配的，因为用户 1 是红色并且用户 2 选择了红色是可以的，而用户 2 是黄色的并且用户 1 选择了黄色是可以的。另一方面，用户 3 和用户 2 将不匹配，因为即使用户 3 是红色并且用户 2 选择红色可以，用户 2 是黄色并且用户 3 说只有橙色可以。

我整天都在想这个问题，但不能完全想出一个解决方案。我的第一个想法是使用 SELECT INNER JOIN 语句，但我不太确定如何因为有这么多可能的组合？然后我尝试使用用户的首选项创建一个临时表，然后使用光标遍历这些首选项，同时检查用户的首选项是否与目标用户的颜色匹配，但我也无法弄清楚。你们能帮忙吗？

标签： mysqlstored-proceduresmariadb

Answer 1

使用该表，只有五种组合指示匹配，您可以在连接中简单地在 a->b 和 a<-b 两个方向上进行 OR 组合：

select *
from myTable a
join myTable b on (
        (a.color = 1 and b.redOK = 1)
        or (a.color = 2 and b.blueOK = 1)
        or (a.color = 3 and b.yellowOK = 1)
        or (a.color = 4 and b.greenOK = 1)
        or (a.color = 5 and b.orangeOK = 1)
    ) and (
        (b.color = 1 and a.redOK = 1)
        or (b.color = 2 and a.blueOK = 1)
        or (b.color = 3 and a.yellowOK = 1)
        or (b.color = 4 and a.greenOK = 1)
        or (b.color = 5 and a.orangeOK = 1)
    )
where a.id <> b.id

或者，您可以更改您的架构，本质上是将您的数据反透视到一个多对多表中。例如：

id ok颜色
1 1
1 2
1 3
1 5
2 1
2 3
2 4
2 5

在这种情况下，您可以通过此表加入：

select distinct a.id, b.id
from myTable a
join okColors aOK on a.id = aOK.id
join myTable b on aOK.okColor = b.color
join okColors bOK on bOK.id = b.id and a.color = bOK.okColor
where a.id <> b.id