mysql My SQL查询中的案例条件问题

yr9zkbsy 于 2023-03-11 发布在 Mysql

关注(0)|答案(2)|浏览(185)

我有一个示例表，我在其中编写了Case语句，但无法编写正确的语法。

SELECT 
    case
        when SECTION = 'A' then
            case
                WHEN (SECTION ='A' AND ROLE = ROLENUMBER) 
                THEN 'GOOD' 
                ELSE 'BAD'
            end
        else 
            case
                when SECTION <> 'A' and ROLE <> 0 and ROLE is not null and ROLENUMBER <> 0 and ROLENUMBER <> null and ROLENUMBER <> ROLE 
                    then 'GOOD' 
                    else  'BAD'  
            when SECTION <> 'A' and (ROLE = 0 or   ROLE IS null) and ROLENUMBER = ROLE 
                then 'GOOD' 
                else  'BAD'  
        end
FROM
    CLASSROOM

除了Case条件外，是否还有其他方法可以在Case条件中写入类似IF条件的内容。
我希望编写类似IF section = 'A' then execute 1st condition ELSE IF section <> 'A'then execute below condition的查询。

mysql

来源：https://stackoverflow.com/questions/75665048/issue-with-case-condition-in-my-sql-query

2条答案

按热度按时间

uidvcgyl1#

这是否囊括了你所有的“好”条件？

case 
     when section = 'A' and role = rolenumber then 'good'
     when section <> 'A' and role <> 0 and role is not null 
       and rolenumber <> 0  and rolenumber <> null and rolenumber <> role then 'good'
     when section <> 'A' and (role = 0 or role is null) and rolenumber = role then 'good'
     else 'bad'
  end

赞(0）回复(0）举报 2023-03-11

9jyewag02#

你可以这样写得更简单些。

SELECT 
    CASE
        WHEN SECTION = 'A' AND ROLE = 'ROLENUMBER' THEN 'GOOD'
        WHEN
            SECTION != 'A' AND ROLE  0
                AND ROLE IS NOT NULL
                AND ROLENUMBER  0
                AND ROLENUMBER  NULL
                AND ROLENUMBER  ROLE
        THEN
            'GOOD'
        WHEN
            SECTION != 'A' AND ROLE = 0
                OR ROLE IS NULL AND ROLENUMBER = ROLE
        THEN
            'GOOD'
        ELSE 'BAD'
    END
FROM
    CLASSROOM

赞(0）回复(0）举报 2023-03-11

我来回答

mysql My SQL查询中的案例条件问题

2条答案

相关问题

热门标签

最新问答