TSQL For Filter Experice From Range multiselect

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

我的表包含经验字段，它是一个整数.并且我的页面包含一个复选框列表，如 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

相关文章