MYSQL存储过程，大小写 [英] MYSQL stored procedure, case

问题描述

我已经浏览了以下页面: http://dev.mysql .com/doc/refman/5.1/en/case.html 以及此版本，但无法通过简单的程序来工作....

I've consulted this page: http://dev.mysql.com/doc/refman/5.1/en/case.html as well as this one, but can't get a simple procedure to work....

更新: 要弄清楚我想做什么:我想从表中选择字段ID为1、0或可以是它们之一的所有行.这是由过程的输入参数指定的，该参数的值为0,1或2.

UPDATE: To clearify what I want to do: I want to select all rows from a table where the field id is either 1, 0 or could be either of them. This is specified by an input parameter to the procedure, which takes values 0,1 or 2.

因此，如果_id = 0，我想要: 从ID = 0的表中选择*

So if _id = 0 I want: select * from TABLE where id = 0

如果_id = 1，我要: 从ID = 1的表中选择*

If _id = 1 I want: select * from TABLE where id = 1

如果_id = 2，我要: 从(0,1)中的id中的表中选择*

And if _id = 2 I want: select * from TABLE where id in (0,1)

我希望如果只让下面的简单案例陈述起作用，我可以自己完成其余的工作...

I was hoping I could get the rest of it to work by myself if I only got the simple case-statement below to work...

我想做的事情是这样的:

What I want to do is something like:

begin
    select * from TABLE where
        case _id
        when  0 then id=0
        else id = 1
        end as id
end

出现错误您的SQL语法有错误".

which gives error "You have an error in your SQL syntax".

我也尝试过:

begin
    select * from TABLE where
        case _id
        when  0 then id=0
        else id=1
        end case
end

给出相同的错误.显然我在某处语法错误，但是我不知道在哪里...有人可以帮助我吗?

which gives the same error. Obviously I've got the wrong syntax somewhere, but I can't figure out where... Can anyone help me out?

谢谢， 尼克拉斯

推荐答案

尝试一下:

begin
    select *,
        case _id
        when 0 then 0
        else 1
        end as id
    from table
end

当用作SELECT查询的一部分时，WHEN不是语句，它是

When used as part of a SELECT query, WHEN is not a statement, it's a control flow function.

您也可以将其表示为:

begin
    select *, _id != 0 as id
    from table
end

这篇关于MYSQL存储过程，大小写的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！