mysql - 存储过程中的多案例选择

Question

SELECT image_id, 
       CASE image_id 
         WHEN 0 THEN (SELECT image_path 
                      FROM   images 
                      WHERE  image_id IN (SELECT default_image 
                                          FROM   registration 
                                          WHERE  reg_id IN (SELECT favorite_id 
                                                            FROM   @favDT))) 
         ELSE (SELECT image_path 
               FROM   images 
               WHERE  image_id = b.image_id 
                      AND active = 1) 
       END AS image_path 
FROM   buddies b 
WHERE  reg_id IN (SELECT favorite_id 
                  FROM   @favDT) 

我在此查询中遇到问题，因为 select favorite_id from @favDT 在这种情况下返回许多最喜欢的 id，但我需要获得在 from 部分中选择的相同最喜欢的 id FROM buddies b where reg_id in (select favorite_id from @favDT) 并在 0 时使用它

有什么帮助吗？

Answer 1

也许是这样的:

SELECT image_id, 
       CASE image_id 
         WHEN 0 THEN (SELECT image_path 
                      FROM   images 
                      WHERE  image_id IN (SELECT default_image 
                                          FROM   registration 
                                          WHERE  reg_id IN (SELECT favorite_id 
                                                            FROM   @favDT
                                                            WHERE favorite_id = b.reg_id
                                                           )
                                         )
                     ) 
         ELSE (SELECT image_path 
               FROM   images 
               WHERE  image_id = b.image_id 
                      AND active = 1) 
       END AS image_path 
FROM   buddies b 
WHERE  reg_id IN (SELECT favorite_id 
                  FROM   @favDT)