case语句选项在两个输出列上拆分 [英] case statement options splitted on two output columns

问题描述

我有一张表，其中列出了我网站的功能.说它有三列:

I have a table with a list of functionalities for my site. Say it has three columns:

id_usr - url - landing_page
1        a.php  a.html
2        b.php  b.html
3        c.php  c.html
4        d.php  d.html

然后我有一张表，其中每个用户都有他可以显示的功能:

Then I have a table where for each user i have those functionalities he can display:

id_usr - func
1         1
1         3

此查询(来自此问题我的)

SELECT    f.id, CASE WHEN id_user IS NOT NULL THEN url ELSE landing_page END
FROM      funzioni f
LEFT JOIN funz_abilitate fa ON fa.id_funzione = f.id AND fa.id_user = $id

返回预期值，但CASE语句的两个选项在同一列中返回.是否可以将它们分为两列(第一列为案例url，第二列为案例Landing_page)?

is returning what is expected but the two options from the CASE statement are returned in one single column. Is it possible to split them in two columns (first for case url and second for case landing_page)?

期望的输出是这样的

id_usr - case url - case landing_page
    1      a.php      NULL
    2      NULL       b.html
    3      c.php      NULL
    4      d.php      NULL

实际上它会返回:

id_usr - case
    1    a.php
    2    b.html
    3    c.php
    4    d.php

推荐答案

您需要2个CASE表达式:

You need 2 CASE expressions:

SELECT    f.id, 
          CASE WHEN id_user IS NOT NULL THEN url END url,
          CASE WHEN id_user IS NULL THEN landing_page END landing_page
FROM      funzioni f
LEFT JOIN funz_abilitate fa ON fa.id_funzione = f.id AND fa.id_user = $id

此外，您还必须使用表的名称/别名来限定所有列名，以免产生歧义.

Also you must qualify all the column names with the table's name/alias to avoid ambiguities.

这篇关于case语句选项在两个输出列上拆分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！