带有 case 语句的 mysql 查询错误地返回一行

Question

我有一个 mysql 查询，它提取相关的域(来自domain_lated)并使用 CASE 来帮助使结果更整洁。问题是返回的这一行是错误的，因为它返回的是 666 而不是 665 的域名:

SELECT case when domain_related.domain_id = '666' then domain_related.domain2_id else domain_related.domain_id end as domain_id, domain.domain_name
FROM domain_related
JOIN domain ON domain.domain_id=domain_related.domain2_id
WHERE '666' in (domain_related.domain_id,domain_related.domain2_id)

665 is actually sofadmiral.net
666 is actually sofranger.it

but the query results are like so:

665 sofranger.it
667 sofmarine.me
668 soffighter.us
669 sofpilot.com
670 sofgeneral.com

有什么想法吗？

Answer 1

经过几天的调整但没有成功，我终于寻找另一种编写此查询的方法，并在利用 UNION 命令/函数中找到了它(见下文)。返回的结果是完美的。感谢大家的帮助!

SELECT domain_id, domain_name
FROM domain
WHERE domain_id IN (
    SELECT domain_id as domain_id
    FROM domain_related
    WHERE domain2_id = 666
    UNION
    SELECT domain2_id as domain_id
    FROM domain_related
    WHERE domain_id = 666
)