开发者

MySQL: value based on conditions

I have a MySQL query where I would like it to have a pseudo column returned which is based off of:

  • If columnW does not equal 0, it should be 1 BUT
  • If columnX does not equal 0, it should be 2 BUT
  • If columnY does not equal 0, it should be 3 BUT
  • If columnZ does not equal 0, it should be 4

Hopefully that makes sense. My SQL isn't good enough to wrap my 开发者_如何学Gohead around this.


Try using a case expression:

SELECT CASE WHEN columnZ <> 0 THEN 4
            WHEN columnY <> 0 THEN 3
            WHEN columnX <> 0 THEN 2
            WHEN columnW <> 0 THEN 1
            ELSE 0
       END AS your_alias_name
FROM ...


You can use CASE ... WHEN to do this.

See

mysql> SELECT CASE 1 WHEN 1 THEN 'one'
    ->     WHEN 2 THEN 'two' ELSE 'more' END;
        -> 'one'
mysql> SELECT CASE WHEN 1>0 THEN 'true' ELSE 'false' END;
        -> 'true'
mysql> SELECT CASE BINARY 'B'
    ->     WHEN 'a' THEN 1 WHEN 'b' THEN 2 END;
        -> NULL
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜