問題描述

我的表包含經驗字段，它是一個整數.并且我的頁面包含一個復選框列表，如 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模板網！