mysql - 在 Mysql 查询中使用 IF

Question

我有两个包含数百万条记录的表，我需要将它们连接起来才能以不同语言显示结果。我希望使用带有 IF 条件的查询来创建 View ，但我无法理解哪种方法是正确的。

我需要做的是:如果下面的选择对单行有一个 NULL(换句话说，该行没有德语翻译)...

SELECT one, two FROM tableA A LEFT JOIN tableB B ON (A.id=B.id) WHERE language='de' order by xyz

...然后为同一行取英文版本，这样选择:

SELECT one, two FROM tableA A LEFT JOIN tableB B ON (A.id=B.id) WHERE language='en' order by xyz

谢谢!卢卡

Answer 1

您的查询很难理解，因为您没有别名。我假设它的结构如下:

select a.one. b.two
from tableA a left join
     tableb b
     on a.id = b.id
where b.language = 'de'
order by xyz

以下提供了应该在 MySQL 中工作的逻辑:

SELECT a.one, 
       (case when (select COUNT(*) from b where b.id = a.id and b.language = 'de' and b.two is null) = 0
             then bde.two
             else bend.two
        end) as two
FROM tableA A LEFT JOIN
     tableB Bde
     ON (A.id=B.id) and
        bde.language='de' left join
     tableB ben
     on ben.language = 'en'
order by xyz

这是假设 NULL 值存储在 B 表中。如果它通过连接丢失，那么它有点难，就像:

SELECT a.one, 
       (case when (select COUNT(*) from a a2 join b b2 on a2.id = b2.id and b2.language = 'de' and b2.two is null) = 0
             then bde.two
             else bend.two
        end) as two
FROM tableA A LEFT JOIN
     tableB Bde
     ON (A.id=B.id) and
        bde.language='de' left join
     tableB ben
     on ben.language = 'en'
order by xyz