SQL查询来有条件地选择一个字段值

By simon at 2018-02-07 • 0人收藏 • 29人看过

我有一个SQL查询连接3个表来返回我所需的数据。该 查询如下:

SELECT (s.user_created, 
        u.first_name, 
        u.last_name, 
        u.email, 
        s.school_name, 
        p.plan_name, 
        substring( a.message, 11), u.phone1) 
FROM cb_school s
inner join ugrp_user u 
    on s.user_created = u.user_id 
inner join cb_plan p 
    on s.current_plan = p.plan_id 
inner join audit a 
    on u.user_id = a.user_id
where s.type = 'sample' 
      and a.module_short = 'sample-user' 
      and s.created_time > current_timestamp - interval '10 day';
查询起作用e如果所有的属性都存在。现在为我的几个 行,下面的值将是a.module_short = 'sample-user'缺失。但是因为我有包含它作为AND条件,那些行将不会被返回。一世 我试图返回一个空字符串的那场如果它存在,否则 值根据我目前的查询。有没有办法做到这一点。

4 个回复 | 最后更新于 2018-02-07
2018-02-07   #1

认为你可以使用CASE WHEN语句,如下所示:

SELECT CASE WHEN a.module_short = 'sample-user' THEN a.module_short
       ELSE '' END AS a.module_short
FROM TableA

2018-02-07   #2

你可以使用COALESCE它返回第一个不为空。

SELECT  COALESCE(a.module_short,'')
FROM TableA  AS a

2018-02-07   #3

这可能是你想要找到所有的行,而不管a.module_short 包含但只显示“样本用户”,如果它是filled与“样本用户”和 否则显示一个NULL。那么你可以使用CASE子句。见下面的一个 例: 为了完成你可以使用[CASE子句](https://docs.microsoft.com / EN-US / SQL / T-SQL /语言元素/案例的Transact-SQL)。看下面的例子。

SELECT 
    Field1, 
    CASE 
        WHEN Field2 = 'b' 
            THEN Field2 
            ELSE NULL                
        END AS Field2,
    Field3
FROM Table
WHERE
    Field1 = 'a' 
    AND (Field3 = 'e') --The brackets aren't needed but can be used like this as well
上面的例子将返回下面的表格: 表:
Field 1 | Field 2 | Field 3.
a           b          e
a           c          e
a           d          e
b           b          e
结果:
Field 1 | Field 2 | Field 3.
a           b          e
a           NULL       e
a           NULL       e
你的问题你想这样:
SELECT (s.user_created, 
        u.first_name, 
        u.last_name, 
        u.email, 
        s.school_name, 
        p.plan_name, 
        substring( a.message, 11), u.phone1),
        CASE 
            WHEN a.module_short = 'sample-user' 
                THEN a.module_short 
                ELSE NULL 
            END AS a.module_short  -- 'ELSE NULL' is not neccesary, anything not matched will result NULL. But added for educational purpose.
FROM cb_school s
inner join ugrp_user u 
    on s.user_created = u.user_id 
inner join cb_plan p 
    on s.current_plan = p.plan_id 
inner join audit a 
    on u.user_id = a.user_id
where s.type = 'sample' 
      and s.created_time > current_timestamp - interval '10 day';

2018-02-07   #4

    SELECT (s.user_created, 
        u.first_name, 
        u.last_name, 
        u.email, 
        s.school_name, 
        p.plan_name, 
        substring( a.message, 11), u.phone1) 
    FROM cb_school s
    INNER JOIN ugrp_user u 
    ON s.user_created = u.user_id 
    INNER JOIN cb_plan p 
    ON s.current_plan = p.plan_id
    INNER JOIN audit a 
    ON u.user_id = a.user_id
    AND a.module_short = 'sample-user' 
    WHERE s.type = 'sample' 
    AND s.created_time > current_timestamp - interval '10 day';

登录后方可回帖

Loading...