代码之家 › 专栏 › 技术社区 › mal

mysql变量有问题,我做错了什么?

mysql-variables if-statement sql mysql

-1

mal · 技术社区 · 6 年前

见下文并测试 here .

我在IF比较和@maleemale变量方面遇到了问题。我可以通过在存储过程中声明变量来实现这一点,没有任何问题,我只想让这该死的东西在上面的在线编辑器中工作,与朋友分享,在这种情况下,只想让这该死的东西工作。我错过了什么?

create table test(id int, gender varchar(10), salary int);
insert into test(id, gender, salary) values (1, 'male', 40000), (2, 'male', 50000),  (3,'male', 40000), (4, 'female', 60000), (5, 'female', 60000), (6,'female', 40000);

set @m =0;
set @f =0;
set @malefemale = 'same';

select count(*) into @m from test WHERE gender like 'male' and salary >= 50000;
select count(*) into @f from test WHERE gender like 'female' and salary >= 50000;

if @m > @f then
    @malefemale = 'male';
else if @f > @m then
    set @malefemale ='female';
endif

select @malefemale;

3 回复 | 直到 6 年前

Gordon Linoff 6 年前

此查询完全不需要变量:

select (case sign(sum(gender = 'male') - sum(gender = 'female') )
             when 1 then 'male'
             when -1 then 'female'
             when 0 then 'same'
        end)
from test 
where gender like 'male' ;

if . 它只允许在编程块中使用——想想存储过程、函数和触发器。

TimSch 6 年前

存储程序的IF语句实现了一个基本的条件构造。

Documentation

然而。如果你能用的话,还有一个。

create table test(id int, gender varchar(10), salary int);
insert into test(id, gender, salary) values (1, 'male', 40000), (2, 'male', 50000),  
(3,'male', 40000), (4, 'female', 60000), (5, 'female', 60000), (6,'female', 40000);

set @m =0;
set @f =0;

select count(*) into @m from test WHERE gender like 'male' and salary >= 50000;
select count(*) into @f from test WHERE gender like 'female' and salary >= 50000;


select if (@m > @f, 'male', 'female')

解释:

dh117 6 年前

我同意你的看法 Gordon Linoff' response

select (case sign(sum(gender = 'male') - sum(gender = 'female') )
             when 1 then 'male' 
             when -1 then 'female'
             when 0 then 'same'
        end)
from test;