如何比较mysql中的用户设置变量? [英] How to compare a user set variable in mysql?

问题描述

我在这里偶然发现了一个先前提出并回答的问题:如何在mysql中对数字字符串使用比较运算符?

I've stumbled on a previously asked and answered question here: How to use comparison operator for numeric string in mysql?

我完全同意答案是最好的提及.但是当我试图创建自己的答案时，它给我自己留下了一个问题.我试图选择第一个数字并将其转换为整数.接下来，我想将该整数与一个数字进行比较(如果出现问题，则为 3).

I absolutely agree with the answer being the best mentioned. But it left me with a question myself while I was trying to create my own answer. I was trying to select the first number and convert it to an integer. Next I wanted to compare that integer with a number (3 in case of the question).

这是我创建的查询:

SELECT experience,CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) AS num FROM employee WHERE @num >= 3;

为了简单起见，假设experience里面的数据是:4-8

For the sake of simplicity, asume the data inside experience is: 4-8

查询没有返回任何错误.但它也不返回数据.我知道可以将列内的数据与用户定义的变量进行比较.但是是否可以像我尝试做的那样将数据(在本例中为整数)与变量进行比较?

The query doesn't return any errors. But it doesn't return the data either. I know it's possible to compare the data inside a column with a user defined variable. But is it possible to compare data (the integer in this case) with the variable like I'm trying to do?

这纯粹是出于好奇和学习.

This is purely out of curiousity and to learn something.

推荐答案

是的，派生表就行.下面的内部选择块是一个派生表.每个派生表都需要一个名称.就我而言，xDerived.

Yes, a derived table will do. The inner select block below is a derived table. And every derived table needs a name. In my case, xDerived.

策略是让派生表清理列名的使用.从派生块中出来的是一个名为 num 的干净列，外部选择可以免费使用.

The strategy is to let the derived table cleanse the use of the column name. Coming out of the derived chunk is a clean column named num which the outer select is free to use.

create table employee
(   id int auto_increment primary key,
    experience varchar(20) not null
);

-- truncate table employee;
insert employee(experience) values 
('4-5'),('7-1'),('4-1'),('6-5'),('8-6'),('5-9'),('10-4');

查询

select id,experience,num 
from 
(   SELECT id,experience, 
    CONVERT(SUBSTRING_INDEX(experience,'-',1),UNSIGNED INTEGER) AS num  
    FROM employee  
) xDerived 
where num>=7;

结果

+----+------------+------+
| id | experience | num  |
+----+------------+------+
|  2 | 7-1        |    7 |
|  5 | 8-6        |    8 |
|  7 | 10-4       |   10 |
+----+------------+------+

注意，你的 @num 概念是错误的，但希望我能解释你上面的意思.

Note, your @num concept was faulty but hopefully I interpreted what you meant to do above.

另外，我选择了 7 而不是 3，因为您的所有样本数据都会返回，我想向您展示它会起作用.

Also, I went with 7 not 3 because all your sample data would have returned, and I wanted to show you it would work.

这篇关于如何比较mysql中的用户设置变量?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！