問題描述
我的表包含經驗字段,它是一個整數.并且我的頁面包含一個復選框列表,如 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的文章就介紹到這了,希望我們推薦的答案對大家有所幫助,也希望大家多多支持html5模板網!