WHERE条件中的SQL IF ELSE / CASE子句 [英] SQL IF ELSE / CASE clause in WHERE condition

问题描述

我有一个非常大的MySQL语句循环通过php foreach和每个循环连接到前一个与union all。我会将语句简化为我的问题的核心，如果需要，我当然也可以在以后要求时添加更多细节。

I have a very large MySQL statement looped through php foreach and each loop connected to the previous with union all. I will simplify the statement to the core of my problem, if needed I can of course also add more details later on request.

我有这个表

+--------+-----------+-----------+
|   ID   |   LANG    |   TITLE   |
+--------+-----------+-----------+
|   1    |    EN     |   T-A     |
+--------+-----------+-----------+
|   1    |    FR     |   T-A     |
+--------+-----------+-----------+
|   2    |    FR     |   T-B     |
+--------+-----------+-----------+
|   3    |    DE     |   T-C     |
+--------+-----------+-----------+
|   3    |    EN     |   T-C     |
+--------+-----------+-----------+

我想在SQL SELECT中写一个 WHERE 条件，该条件应该显示每个ID最多一个结果。但只有当LANG为FR或EN 时，它才应显示结果。在顶部 FR应优先，如果没有可用于ID的FR，则EN应仅显示为替代。所以结果看起来像这样。

I want to write a WHERE condition in the SQL SELECT that should show me for each ID maximum one result. But it should show results only if LANG is FR or EN. On top FR should be prefered and EN should only be displayed as alternative if no FR is available for the ID. So the result would look like this.

+--------+-----------+-----------+
|   ID   |   LANG    |   TITLE   |
+--------+-----------+-----------+
|   1    |    FR     |   T-A     |
+--------+-----------+-----------+
|   2    |    FR     |   T-B     |
+--------+-----------+-----------+
|   3    |    EN     |   T-C     |
+--------+-----------+-----------+

我尝试用IF-ELSE / CASE自己构建一些东西，但我对SQL不是很有经验，所以任何帮助都会有很大的帮助。

I have tried to build something by myself with IF - ELSE / CASE but I am not very experienced with SQL so any help would be much appreaciated.

我试过的简化SQL就像

A simplified SQL I tried would be something like

SELECT * FROM `table` 
WHERE `table`.`ID` = 1
IF `table`.`LANG` = 'FR' 
BEGIN
  AND `table`.`LANG` = 'FR' 
END
ELSE
BEGIN
  AND `table`.`LANG` = 'EN' 
END

union all 
SELECT * FROM `table` 
WHERE `table`.`ID` = 2
IF `table`.`LANG` = 'FR' 
BEGIN
  AND `table`.`LANG` = 'FR' 
END
ELSE
BEGIN
  AND `table`.`LANG` = 'EN' 
END

union all 
SELECT * FROM `table` 
WHERE `table`.`ID` = 3
IF `table`.`LANG` = 'FR' 
BEGIN
  AND `table`.`LANG` = 'FR' 
END
ELSE
BEGIN
  AND `table`.`LANG` = 'EN' 
END

Sitenote我可能不会使用ORDER BY与LIMIT 1结合的任何构造，因为我正在循环SQL每次循环多次使用php。

Sitenote I may not use any construct with ORDER BY combined with LIMIT 1 since I am looping the SQL through a php for each loop multiple times.

编辑：对我有用的解决方案

SELECT * FROM `table1`
WHERE ID = 1
AND lang = 'FR'
OR (lang = 'EN' AND ID NOT IN (SELECT ID FROM table1 WHERE lang = 'FR'))

推荐答案

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

The most elegant and efficient solution with a sophisticated query optimizer would be this:

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'
) t1
WHERE id = ?

这会为您提供所需的结果，按ID过滤。如果优化器无法按下 id =？，您可能必须自己动手才能获得不错的性能：

This gives you the desired result, filtered by ID. In case the optimizer is however not able to push down the id = ? you might have to do it yourself to get decent performance:

  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：

However, if you can, I would get all the results at once and not iterate over the IDs in the first place:

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'

这将为您提供所有标有EN且没有相应FR加上所有FR的ID。或者你也可以尝试：

This will get you all the IDs that have lang 'EN' and no corresponding 'FR' plus all the 'FR's. Alternatively you could also try:

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))

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

But my guess would be the first query is fastest.

这篇关于WHERE条件中的SQL IF ELSE / CASE子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！