mysql - 如何在 GROUP_CONCAT 中使用 ELSE IF？

Question

我在 mySQL 查询中有一个 IF 语句，并且运行良好:

 GROUP_CONCAT(DISTINCT CONCAT("animal:",if(animal.name="monkey","fred",""),",color: ",animal.color) SEPARATOR " <br>") AS animals

我现在想添加一个 ELSEIF:

  GROUP_CONCAT(DISTINCT CONCAT("animal:",if(animal.name="monkey","fred","")elseif(animal.name="cat","jane",""),",color: ",animal.color) SEPARATOR " <br>") AS animals

但是我收到一条错误消息:

Syntax error or access violation: 1064 You have an error in your SQL syntax

Answer 1

“if elseif endif”是一个控制语句，而 if() 是 MySQL 中的一个函数。虽然它们具有相同的名称，但它们是不同的东西。

如果有多个选项，可以使用 CASE 代替 IF。

SELECT 
    GROUP_CONCAT(DISTINCT CONCAT("animal:",
      CASE animal.name
        WHEN "monkey" THEN "fred"
        WHEN "cat" THEN "jane"
        ELSE "other"
      END,
      ",color: ",animal.color) SEPARATOR " <br>"
    ) AS animals
FROM (
  SELECT 'monkey' AS 'name', 'brown' AS 'color'
  UNION
  SELECT 'cat', 'white'
  UNION
  SELECT 'dog', 'black'
  ) AS animal