求一个特殊扩大查询的方法

[水晶烟灰缸]

数据库中有数据
ID PID VALUE
1 100 2
2 100 4
3 100 5
4 100 1
1 101 3
2 101 4
3 101 0
1 102 2
2 102 5
3 102 1

查询出所有VALUE介于（包含）2~4的值，查询结果如下
ID PID VALUE
1 100 2
2 100 4
1 101 3
2 101 4
1 102 2

特殊要求每个查询结果自动扩大查询一位，期望结果如下
ID PID VALUE
1 100 2
2 100 4
3 100 5
1 101 3
2 101 4
3 101 0
1 102 2
2 102 5
3 102 1

如何实现啊

[永生天地]

规律不太明确，帮顶

[中国风]

select * from tabel1 where value between 2 and 4

查询2没看出规律

[中国风]

?每个查询结果自动扩大查询一位

在where条件里加上特定条件

[Mr_Nice]

--> 测试数据：[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[PID] int,[VALUE] int)
insert [TB]
select 1,100,2 union all
select 2,100,4 union all
select 3,100,5 union all
select 4,100,1 union all
select 1,101,3 union all
select 2,101,4 union all
select 3,101,0 union all
select 1,102,2 union all
select 2,102,5 union all
select 3,102,1

select * from [TB]


SELECT * FROM TB WHERE [VALUE] BETWEEN 2 AND 4

/*ID          PID         VALUE
----------- ----------- -----------
1           100         2
2           100         4
1           101         3
2           101         4
1           102         2

(5 行受影响)
*/

SELECT * FROM TB WHERE ID <4 

/*
ID          PID         VALUE
----------- ----------- -----------
1           100         2
2           100         4
3           100         5
1           101         3
2           101         4
3           101         0
1           102         2
2           102         5
3           102         1

(9 行受影响)*/

???这样???

[xyj052]

Declare @i int,@j int
set @i=2
set @j=4
--普通
select * from biao where VALUE between @i and @j
--特殊
select * from biao where VALUE between @i-1 and @j+1

这样？？？

[水晶烟灰缸]

我的特殊要求就是，就是把同PID的数据，扩大一位查询
如果前面有数据，则将前一位纳入查询结果；如果后面有数据，则将后一位纳入查询结果

如标准查询是BETWEEN 4 AND 5

ID PID VALUE
1 100 2
2 100 4
3 100 5
4 100 1
1 101 3
2 101 4
3 101 0
1 102 2
2 102 5
3 102 1

标准返回结果

ID PID VALUE
1 100 2
2 100 4
1 101 3
2 101 4
1 102 2

特殊要求返回结果

ID PID VALUE
1 100 2 --由于PID=100的第二个数据被查询到第一个数据没有被查询到，扩大一位查询范围，所以被查询到
2 100 4
3 100 5
4 100 1 --由于PID=100的第三个数据被查询到第一个数据没有被查询到，扩大一位查询范围，所以被查询到
1 101 3 --由于PID=101的第二个数据被查询到第一个数据没有被查询到，扩大一位查询范围，所以被查询到
2 101 4
3 101 0 --由于PID=101的第二个数据被查询到第一个数据没有被查询到，扩大一位查询范围，所以被查询到
1 102 2 --由于PID=102的第二个数据被查询到第一个数据没有被查询到，扩大一位查询范围，所以被查询到
2 102 5
3 102 1 --由于PID=102的第二个数据被查询到第一个数据没有被查询到，扩大一位查询范围，所以被查询到

[xman_78tom]

declare @tab table (id int,pid int,value int);
insert into @tab
 select 1,100,2 union all select 2,100,4 union all
 select 3,100,5 union all select 4,100,1 union all
 select 1,101,3 union all select 2,101,4 union all
 select 3,101,0 union all select 1,102,2 union all
 select 2,102,5 union all select 3,102,1;
with t1 as(
select id,pid,value from @tab where value between 4 and 5
),
t2 as(
select pid,max(id) [max],min(id) [min] from t1 group by pid
)
select t.*
from @tab t, t2 
where t.pid=t2.pid and (t.id=t2.[max]+1 or t.id=t2.[min]-1)
union all
select * from t1 order by 2;
/*
1	100	2
2	100	4
3	100	5
4	100	1
1	101	3
2	101	4
3	101	0
1	102	2
2	102	5
3	102	1
*/

[xman_78tom]

-- or
select * from @tab t 
where id=(select max(id)+1 from @tab 
where value between 4 and 5 and pid=t.pid)
union all
select * from @tab t 
where id=(select min(id)-1 from @tab 
where value between 4 and 5 and pid=t.pid)
union all
select id,pid,value from @tab 
where value between 4 and 5
order by 2

[nightmaple]

不知所云

select * from [tb] where PID between 
(select MIN(pid) from [tb] where value between 4 and 5)
and 
(select max(pid) from [tb] where value between 4 and 5)

[xiaoliaoyun]

declare @x  int
declare @y  int
set @x = 2
set @y = 4
declare @tab table (id int,pid int,value int);
insert into @tab
select 1,100,2 union all select 2,100,4 union all
select 3,100,5 union all select 4,100,1 union all
select 1,101,3 union all select 2,101,4 union all
select 3,101,0 union all select 1,102,2 union all
select 2,102,5 union all select 3,102,1;

with temp
as
(
    select *,px = row_number() over(partition by pid order by value) from @tab
)
select distinct b.id,b.pid,b.value 
from (select * from temp where value between @x and @y) a
inner join temp b on a.pid = b.pid and (a.px + 1 = b.px or a.px - 1 = b.px or a.px = b.px)
order by b.pid,b.id