如何在sql server 2008中的值之间获取准确 [英] how to get exact between values in sql server 2008

问题描述

我有一个包含2列minexperience和maxexperience的表，作为varchar(max)
这些值是

Hi,

I have a table with 2 columns minexperience and maxexperience as varchar(max)
those values are

minexperience   maxexperience 
1                   4
2                   5
1                   3
8                   9

当我通过传递两个参数值进行检索时，它们是@minexp和@maxexp

我写了如下查询

when i am retrieving by passing two parameter values those are @minexp and @maxexp

I wrote the query as follows

select *from table where minexperience between @minexp and @maxexp and maxexperience between @minexp and @maxexp

那我的输出就错了.

当iam通过1和3获得正确的输出时.
但是当iam通过8和9却没有输出

我该如何写查询

then I am getting wrong output.

when iam passing 1 and 3 getting correct output.
but when iam passing 8 and 9 getting no output

how can i write the query

推荐答案

问题可能是您的数据不是您想的那样:如果要与数字进行任何匹配，请不要将数字存储为字符串-请改用数字格式.您的"8"或"9"不仅可能是"8"或"9"，还可能有前导空格.

改用Int数据类型.

The problem is probably that your data is not what you think: never store numbers as strings if you intend to do any match with them - use a number format instead. The chances are that your "8" or "9" is not just "8" or "9" - it may have leading spaces for example.

Use an Int datatype instead.

您肯定只需要此

You only need this surely

select *from table where minexperience >= @minexp and maxexperience <= @maxexp

这很好用

This works fine

create table yourTableName(minexperience nvarchar(max),   maxexperience nvarchar(max))
insert into yourTableName
select 1,                           4
union all select 2,                           5
union all select 1,                           3
union all select 8,                           9

declare @minexp nvarchar(max) = 8, 
		@maxexp nvarchar(max) = 9
select *from yourTableName where minexperience >= @minexp and maxexperience <= @maxexp

我们得到的输出为

we get the output as

minexperience    maxexperience
---------------- ----------------
8                9

这篇关于如何在sql server 2008中的值之间获取准确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！