根据菜单中的类型提取新闻/文章/子页面参数

Question

我编写了一个简单的 sql 查询 - http://pastebin.com/AdJAabzb - 但它根本不是最佳的。 我想在当 menu.type == news 时对 'news' 进行外连接，或者当 menu.type == 'article' 时对 'articles' 进行外连接，或者当 menu.type == 'page' 时对 'subpages' 进行外连接。如果类型不同（例如 none 或 ext），seo_filename 应为 NULL。

SELECT 
 menu.*
 , news.seo_filename AS news_seo_filename
 , articles.seo_filename AS article_seo_filename
 , subpages.seo_filename AS subpage_seo_filename
FROM menu
LEFT OUTER JOIN news ON menu.link = news.id
LEFT OUTER JOIN articles ON menu.link = articles.id
LEFT OUTER JOIN subpages ON menu.link = subpages.id 
ORDER BY lft

如何使用带有 if'f 或 sth 的一个查询来完成此操作？

Answer 1

我的“不聪明的联盟”解决方案。这在 Postgres 中是合理的，因为键字段可以从子查询中提升出来。

注意：对 UNION 的需求几乎总是数据模型中设计缺陷的结果。

WITH  un AS (
  SELECT 'articles' AS menutype
  , ar.id AS id
  , ar.seo_filename AS filename
  FROM articles ar
  UNION
  SELECT 'news' AS menutype
  , ne.id AS id
  , ne.seo_filename AS filename
  FROM news ne
  UNION
  SELECT 'pages' AS menutype
  , pa.id AS id
  , pa.seo_filename AS filename
  FROM pages pa
  ) 
SELECT
 me.*
 , un.filename
 FROM menu me
  , un
 WHERE me.menutype = un.menutype
 AND me.id = un.id
  ;

编辑：
注意：我稍微更改了列名......

Answer 2

尝试使用 CASE：

SELECT 
    M.*,
    CASE(M.type)
        WHEN 'news' THEN N.seo_filename
        WHEN 'articles' THEN A.seo_filename
        WHEN 'page' THEN S.seo_filename
        ELSE NULL
    END AS seo_filename
FROM 
    menu M
        LEFT OUTER JOIN news N ON M.link = N.id
        LEFT OUTER JOIN articles A ON M.link = A.id
        LEFT OUTER JOIN subpages S ON M.link = S.id 
ORDER BY 
    M.lft 

我不确定这个 lft 字段来自哪里，但我假设来自表格菜单。

Answer 3

另一种可能的解决方案：

SELECT 
 menu.*
 , COALESCE( news.seo_filename, articles.seo_filename, subpages.seo_filename)
   AS seo_filename
FROM menu
LEFT OUTER JOIN news ON menu.link = news.id
LEFT OUTER JOIN articles ON menu.link = articles.id
LEFT OUTER JOIN subpages ON menu.link = subpages.id 
ORDER BY lft
;