查找具有可互换行的类似条目

Question

我确实有一个带有NPC条目的MySQL表。每个NPC有3个技能槽。每个插槽有两个可能的值。

我正在努力寻找类似的NPC。类似的意思如下:每个NPC可以建立相同的能力组合与这6个能力。插槽是可互换的，但a值和b值总是在一起.

在下表中，全国人大40和41将是相似的。NPC 42与它们不同，因为它不能构建相同的能力组合，即使是6个法术it是相同的：

NPC 40和41可以使用能力: 492+429+538；

NPC 42不能这样做，因为492和429处于同一位置。

NPC
+-----+---------+---------+---------+---------+---------+---------+
| id  | slot_1a | slot_1b | slot_2a | slot_2b | slot_3a | slot_3b |
+-----+---------+---------+---------+---------+---------+---------+
| 39  |    384  |    202  |    389  |    392  |    459  |     278 |
| 40  |    429  |    535  |    492  |    357  |    538  |     536 |
| 41  |    492  |    357  |    429  |    535  |    538  |     536 |
| 42  |    492  |    429  |    357  |    535  |    538  |     536 |
+-----+---------+---------+---------+---------+---------+---------+

如果我没有弄错，有48个可能的组合为类似的NPC。现在，我想知道是否有一个更简单的解决方案，找到类似的NPC，然后检查所有48个组合？

SQL (新数据)：http://sqlfiddle.com/#!9/4c1aae/1

编辑：忘了提到，每个插槽的值也可以转换它们的位置。这可能就是@不能100%工作的原因。

Answer 1

我几乎肯定有更好的方法，但你可以试试这个。你必须检查每一个插槽组合，如果与其他的匹配。

SQL Fiddle

SELECT aa.npc_id, bb.npc_id
FROM npc AS aa, npc AS bb
WHERE 
( (
    aa.slot_1a = bb.slot_1a AND aa.slot_1b = bb.slot_1b) 
    OR (aa.slot_1a = bb.slot_2a AND aa.slot_1b = bb.slot_2b) 
    OR (aa.slot_1a = bb.slot_3a AND aa.slot_1b = bb.slot_3b) 
)
AND 
( (
    aa.slot_2a = bb.slot_1a AND aa.slot_2b = bb.slot_1b) 
    OR (aa.slot_2a = bb.slot_2a AND aa.slot_2b = bb.slot_2b) 
    OR (aa.slot_2a = bb.slot_3a AND aa.slot_2b = bb.slot_3b) 
)
AND 
( (
    aa.slot_3a = bb.slot_1a AND aa.slot_3b = bb.slot_1b) 
    OR (aa.slot_3a = bb.slot_2a AND aa.slot_3b = bb.slot_2b) 
    OR (aa.slot_3a = bb.slot_3a AND aa.slot_3b = bb.slot_3b) 
)
AND aa.npc_id != bb.npc_id;

Answer 2

也许不是最好的解决方案，但我就是这样结束解决这个问题的：

SELECT group_concat( npc_id separator ','),
@slot1:= concat_ws(',',least(slot_1a,slot_1b),greatest(slot_1a,slot_1b)) AS slot1,
@slot2:= concat_ws(',',least(slot_2a,slot_2b),greatest(slot_2a,slot_2b)) AS slot2,
@slot3:= concat_ws(',',least(slot_3a,slot_3b),greatest(slot_3a,slot_3b)) AS slot3,
@min:=least(least(@slot1,@slot2),@slot3) as min,
@max:=greatest(greatest(@slot1,@slot2),@slot3) as max,
@med:=(
CASE
    when @slot1 != @min and @slot1 != @max then @slot1
    when @slot2 != @min and @slot2 != @max then @slot2
    when @slot3 != @min and @slot3 != @max then @slot3
END
) AS med,
concat_ws(',',@min,@med,@max) AS move_set
FROM npc
GROUP by move_set