WHERe条件下的SQL IF ELSE / CASE子句

使用复杂的查询优化器的最优雅，最有效的解决方案是：

SELECt * FROM (    SELECt * FROM `table`    WHERe ID IN (      SELECt id FROM `table`       WHERe lang = 'EN'      EXCEPT      SELECt id FROM `table`      WHERe lang = 'FR'    ) OR table.LANG ='FR') t1WHERe id = ?

这将为您提供所需的结果，并按ID过滤。但是，如果优化器无法降低性能，则

id = ?

  SELECt * FROM `table`    WHERe id = ? AND (ID IN (      SELECt id FROM `table`       WHERe lang = 'EN' AND ID = ?      EXCEPT      SELECt id FROM `table`      WHERe lang = 'FR' AND ID = ?    ) OR table.LANG ='FR')

但是，如果可以的话，我将立即获得所有结果，而不是首先遍历ID：

SELECt * FROM `table`WHERe ID IN (  SELECt id FROM `table`   WHERe lang = 'EN'  EXCEPT  SELECt id FROM `table`  WHERe lang = 'FR') OR table.LANG ='FR'

这将为您提供所有带有lang’EN’且没有相应的’FR’以及所有’FR’的ID。另外，您也可以尝试：

SELECt * FROM `table`WHERe lang = 'FR'OR (lang = 'EN' AND ID NOT IN (SELECt ID FROM table WHERe lang = 'FR'))

或者

SELECt * FROM `table`WHERe lang = 'FR'OR (table.LANG = 'EN' AND NOT EXISTS (SELECt * FROM table t1 WHERe lang = 'FR' AND t1.id = table.id))

但是我的猜测是第一个查询最快。