条件SQL选择-当返回集为空时，执行另一个选择?

I have webpage that has its "pages/articles" stored in a MySQL database. It also has a feature to show a different side menu for different pages. All that (menus, menu's items) is stored in DB.

我有一个网页，它的“页面/文章”存储在MySQL数据库中。它还有一个特性，可以为不同的页面显示不同的侧边菜单。所有这些(菜单、菜单项)都存储在DB中。

Here's my SQL for getting all menu items for current page:

下面是我的SQL，用于获取当前页面的所有菜单项:

SELECT * 
FROM menu_items 
   JOIN pages 
    ON menu_items.menu_id=pages.right_menu_id 
WHERE pages.link = "some_link"

and it works.

和它的工作原理。

What I want is, when this query returns an empty set, to execute another query and get its result instead. Is it possible ?? If query mentioned above is empty, I would like to get result of this query:

我想要的是，当这个查询返回一个空集时，执行另一个查询并获得它的结果。是否有可能?如果上面提到的查询是空的，我想得到这个查询的结果:

SELECT * 
FROM menu_items 
WHERE menu_id=2;

Is it possible, or should I just do it in PHP ?

有可能，还是我应该用PHP来写?

2 个解决方案

#1

You could use the EXIST function to test whether the current page has any links:

您可以使用EXIST函数来测试当前页面是否有链接:

IF EXISTS(SELECT menu_id FROM menu_items JOIN pages ON menu_items.menu_id = pages.right_menu_id WHERE pages.link = "some_link") THEN

    SELECT   * 
    FROM     menu_items 
             JOIN pages 
                 ON menu_items.menu_id=pages.right_menu_id 
    WHERE    pages.link = "some_link" 

ELSE

    SELECT   * 
    FROM     menu_items 
             JOIN pages 
                 ON menu_items.menu_id=pages.right_menu_id 
    WHERE    menu_id = 2

END IF

You could also try something like this:

你也可以尝试以下方法:

DECLARE @LinkCount INT
SELECT @LinkCount = COUNT(*) FROM menu_items JOIN pages ON menu_items.menu_id = pages.right_menu_id WHERE pages.link = "some_link"

SELECT   * 
FROM     menu_items 
         JOIN pages 
             ON menu_items.menu_id=pages.right_menu_id 
WHERE    (@LinkCount > 0 AND pages.link = "some_link") OR (@LinkCount = 0 AND menu_id = 2)

There might be more elegant ways of doing this, but hope this helps.

也许有更优雅的方法可以做到这一点，但希望这能有所帮助。

2 个解决方案

#1

更多相关文章

随机推荐