选择更好的结果匹配

Question

我有个小问题要有个好的解决办法。我需要寻找更好的结果。

我有三个表，规则，意思和rule_meaning。规则可以有一个或更多的含义。

这是结果

SELECT * FROM rule, rule_meaning, meaning WHERE rule_meaning.rule_id = rule.id AND rule_meaning.meaning_id = meaning.i

rule.id rule.name   rule.id meaning_id  meaning.id  meaning.name
1   RADIO - Ecouter   1        1          1          ACTIVER
1   RADIO - Ecouter   1        6          6          RADIO
2   LUMIERE Allumer   2        1          1          ACTIVER
3   RADIO + volume    3        6          6          RADIO
4   RADIO - volume    4        6          6          RADIO

当我搜索激活器和无线电时，我只想要无线电耦合器(rule.id = 1)。

我试过了

• 其中meaning.name =活动器和meaning.name =无线电但没有结果
• 其中，meaning.name = ACTIVER或meaning.name =射电，我有几个结果。

Answer 1

您希望选择同时具有活动和无线电含义的规则。您可以通过聚合来完成这一任务：

select *
from rule
where id in
(
  select rule_id
  from rule_meaning 
  where meaning_id in (select id from meaning where name in ('ACTIVER', 'RADIO'))
  group by rule_id
  having count(*) = 2 -- both meanings
);

另一种我喜欢的方法是使用EXISTS或IN分别查找每个值。我在这里展示它，因为它是如何处理这项任务的简单方法。

select *
from rule
where id in
(
  select rule_id
  from rule_meaning 
  where meaning_id = (select id from meaning where name = 'ACTIVER')
)
and id in
(
  select rule_id
  from rule_meaning 
  where meaning_id = (select id from meaning where name = 'RADIO')
);

Answer 2

您可能需要使用case语句，例如

    CASE WHEN WHERE meaning.name = ACTIVER AND meaning.name = RADIO
    THEN ....
    ELSE

但我觉得你要问的是

    WHERE meaning.name = ACTIVER AND rule.name = RADIO 
    AND rule.id = 1