我的表包含经验字段,它是一个整数.并且我的页面包含一个复选框列表,如 0-3,3-7,7-9,9-12,12-15,15+ 年,我必须使用选择查询从表中过滤它我试过 between
but it is not working when multiple fields selected can any one help
My Table contain Experience Field which is an integer . and my page contains a check box list like 0-3,3-7,7-9,9-12,12-15,15+ years and i have to filter this from table using select query i have tried between
but it is not working when multiple fields selected can any one help
我的表结构就像
Name Experience in year
---- ---------
a 1
b 2
c 3
d 5
e 2
f 1
我的数据库参数是一个 varchar 字符串
My parameter for database is a varchar string
if we select 0-3years then '0-3'
if we select 3-6years then '3-6'
if we select both then '0-3,3-6'
if we select 0-3years and 9-12years then '0-3,9-12'
现在我以这些格式发送数据我不知道这是一个好方法请告诉我更好的方法
Now i am sending Data in these format i dont know it is a good method please show me the better way
首先你需要一个表 checkRanges
First you need a table checkRanges
CREATE TABLE checkRanges
([checkID] int, [name] varchar(8), [low] int, [upper] int);
INSERT INTO checkRanges
([checkID], [name], [low], [upper])
VALUES
(1, '0-3', 0, 2),
(2, '3-6', 3, 5),
(4, '6-9', 6, 8),
(8, '9-12', 9, 11),
(16, '12+', 12, 999)
看到 checkID
是 2 的幂吗?
See how checkID
are power of 2?
在您的应用中,如果用户选择 3-6
和 9-12
,您将 2+8 = 10
发送到您的数据库.如果您使用数据库信息创建复选框也会很棒.
In your app if user select 3-6
and 9-12
you send 2+8 = 10
to your db. Also would be great if you create your check box using the db info.
在您的数据库中,您进行按位比较以选择正确的范围.然后对每个范围执行 between.
In your db you do bitwise comparasion to select the right ranges. Then perfom the between with each range.
WITH ranges as (
SELECT *
FROM checkRanges
where checkID & 10 > 0
)
SELECT *
FROM users u
inner join ranges r
on u.Experience between r.low and r.upper
一起查看SQL Fiddle 演示我包括更多的用户.您只需要更改条款 where checkID &10 >0
测试其他组合.
See it all together SQL Fiddle Demo
I include more users. You only have to change the clausule where checkID & 10 > 0
to test other combination.
注意:
我更新了范围.将上限值更改为 value - 1
,因为 between
是包含性的,可能会产生重复的结果.
NOTE:
I update the ranges. Change the upper value to value - 1
because between
is inclusive and could give duplicate results.
如果要使用旧版本,您必须将连接语句中的betwewen
替换为
If want use old version you have to replace the betwewen
in the join sentence to
u.Experience >= r.low and u.Experience *<* r.upper
这篇关于TSQL For Filter Experice From Range multiselect的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!