mysql - 嵌套 select 语句 mysql 中的 IFNULL

Question

当在嵌套 SELECT 语句中找不到行时，我想返回一条消息，如下所示。在本例中，当连续没有 Lake 时，返回消息为“no Lake found”。我尝试了 IFNULL 但当没有湖泊时它不会返回任何内容。

SELECT GROUP_CONCAT(concat_ws(', ', Lake_name, 
                                    IFNULL(Area, 'not available'),                             
                                    IFNULL(Altitude, 'not available'), 
                                    IFNULL(Depth, 'not available'), 
                                    IFNULL(River_outflow, 'not available')
                             ) SEPARATOR '; ')
From LAKE WHERE IFNULL(Lake_name, 'no lakes found') IN
                      (SELECT  Lake FROM LOCATION where County IN 
                      (SELECT County FROM CITY WHERE City_name='$userinput')) GROUP BY NULL;";   

Answer 1

我认为你应该选择这种方式

  SELECT CASE when Lake_name is null then 'no lakes found' else 
       GROUP_CONCAT(concat_ws(', ', Lake_name, 
                                      IFNULL(Area, 'not available'),                             
                                      IFNULL(Altitude, 'not available'), 
                                      IFNULL(Depth, 'not available'), 
                                      IFNULL(River_outflow, 'not available')
                               ) SEPARATOR '; ') END my_lake
  From LAKE 
  WHERE LAKE_name  IN  (SELECT  Lake_name FROM LOCATION where County IN 
                        (SELECT County FROM CITY WHERE City_name='$userinput')) 
  OR LAKE_name is null ;