mysql - 在 MySQL 选择查询中嵌套 if/else

Question

我在为嵌套的 mysql if 语句设置正确的语法时遇到了一些麻烦。这是我当前的查询，很好。但是我需要的是父类。这是让我感到困惑的地方。

基本上我需要将 parentid 位添加到我的查询中(用伪代码编写)

      SELECT  p.*,i.`image`,

            ( IF(`limitc1`.`parentid` IN (135,136), `limitc1`.parentid)
              ELSEIF(`limitc2`.`parentid` IN (135,136), `limitc2`.parentid)
              ELSEIF(`limitc3`.`parentid` IN (135,136), `limitc3`.parentid)
              ELSEIF(`limitc4`.`parentid` IN (135,136), `limitc4`.parentid)
            ) as `parentid`
            FROM `product` p
             JOIN `productcategory` limitpc USING(productid)
                LEFT JOIN `category` limitc1 ON (limitpc.`categoryid` = limitc1.`categoryid`)
                LEFT JOIN `category` limitc2 ON (limitc1.`parentid` = limitc2.`categoryid`)
                LEFT JOIN `category` limitc3 ON (limitc2.`parentid` = limitc3.`categoryid`)
                LEFT JOIN `category` limitc4 ON (limitc3.`parentid` = limitc4.`categoryid`)
            LEFT JOIN `productlink` l ON (p.`productid` = l.`targetid` OR p.`productid` = l.`sourceid`)
            JOIN `productimage` i ON p.`productid` = i.`productid` AND i.`primary` = 1
            WHERE (l.`sourceid` = 5471 OR l.`targetid` = 5471)
            AND p.`visible` = 1
            AND p.`websitevisible` = 1
            AND p.`productid` != 5471
            GROUP BY p.`productid`
            LIMIT 8

任何想法将不胜感激! :-)

Answer 1

您想使用case 语句:

 SELECT  p.*,i.`image`,
         (case when `limitc1`.`parentid` IN (135,136) then `limitc1`.parentid
               when `limitc2`.`parentid` IN (135,136) then `limitc2`.parentid
               when `limitc3`.`parentid` IN (135,136) then `limitc3`.parentid
               when `limitc4`.`parentid` IN (135,136) then `limitc4`.parentid
         end) as `parentid`
  . . .

这不仅做你想做的事，而且还是标准的 SQL。 if 语句是 MySQL 特有的。