有这样一个表格
id name num
11 a 1
11 b 2
11 g 3
11 d 4
11 e 5
11 f 6
11 c 7
11 h 8
11 i 9
11 h 10
22 a 11
22 w 12
22 d 13
22 g 14
22 h 15
22 g 16
22 k 17
22 b 18
22 c 19当输入俩个数时,如a,c,要求查询出同一id里面a和c之间的所有name,跟公交车站的问题很像,这表能不能实现啊?
id name num
11 a 1
11 b 2
11 g 3
11 d 4
11 e 5
11 f 6
11 c 7
11 h 8
11 i 9
11 h 10
22 a 11
22 w 12
22 d 13
22 g 14
22 h 15
22 g 16
22 k 17
22 b 18
22 c 19当输入俩个数时,如a,c,要求查询出同一id里面a和c之间的所有name,跟公交车站的问题很像,这表能不能实现啊?
--> (让你望见影子的墙)生成测试数据,时间:2008-12-07
if not object_id('tb') is null
drop table tb
Go
Create table tb([id] int,[name] nvarchar(1),[num] int)
Insert tb
select 11,N'a',1 union all
select 11,N'b',2 union all
select 11,N'g',3 union all
select 11,N'd',4 union all
select 11,N'e',5 union all
select 11,N'f',6 union all
select 11,N'c',7 union all
select 11,N'h',8 union all
select 11,N'i',9 union all
select 11,N'h',10 union all
select 22,N'a',11 union all
select 22,N'w',12 union all
select 22,N'd',13 union all
select 22,N'g',14 union all
select 22,N'h',15 union all
select 22,N'g',16 union all
select 22,N'k',17 union all
select 22,N'b',18 union all
select 22,N'c',19
Go
Select * from tbselect * from
tb
where name between 'a' and 'c' 11 a 1
11 b 2
11 c 7
22 a 11
22 b 18
22 c 19
declare @a nvarchar(1),@b nvarchar(1)
select @a=N'a',@b=N'c'
;with aa as
(
select id,name,row_number() over(partition by id order by num) row from tb
),
bb as
(
select *,val=case when name=@a then 1 else 0 end from aa where row=1
union all
select aa.*,val=case when bb.name=@b then 0 when bb.val=1 or aa.name=@a then 1 else 0 end from aa inner join bb on aa.row=bb.row+1 and aa.id=bb.id
)
select * from bb where val=1 order by id,row 试试这个
select id,name
from tb a
where num between (select num from tb where name='a' and id=a.id) and (select num from tb where name='c' and id=a.id)
/**
id name
----------- ----
11 a
11 b
11 g
11 d
11 e
11 f
11 c
22 a
22 w
22 d
22 g
22 h
22 g
22 k
22 b
22 c(所影响的行数为 16 行)
**/
where num between(select num from tb where name='a' and id=t.id) and (select num from tb where name='c' and id=t.id)11 a 1
11 b 2
11 g 3
11 d 4
11 e 5
11 f 6
11 c 7
22 a 11
22 w 12
22 d 13
22 g 14
22 h 15
22 g 16
22 k 17
22 b 18
22 c 19这样?
--引用1搂数据
DECLARE @INTID INT
SET @INTID=11 --select id,name,num from tb a
where num between (select num from tb where name='a' and id=a.id) and (select num from tb where name='c' and id=a.id)
and id=@intid
from tb as b
left join(
select id,min(case when [name] = 'a' then num end) as mi,
max(case when [name] = 'c' then num end) as ma
from tbgroup by id) a
on a.id = b.id
from tb as b
left join(
select id,min(case when [name] = 'a' then num end) as mi,
max(case when [name] = 'c' then num end) as ma
from tbgroup by id) a
on a.id = b.id
where b.num between a.mi and a.ma
select *from tb a
where station_num
between
case when (select station_num from tb where station_name ='西斜六路' and line_id=a.line_id)> (select station_num from tb where station_name = '公交五公司' and line_id=a.line_id)
then (select station_num from tb where station_name = '公交五公司' and line_id=a.line_id)
else (select station_num from tb where station_name ='西斜六路' and line_id=a.line_id)
end
and case when (select station_num from tb where station_name ='西斜六路' and line_id=a.line_id)> (select station_num from tb where station_name = '公交五公司' and line_id=a.line_id)
then (select station_num from tb where station_name = '西斜六路' and line_id=a.line_id)
else (select station_num from tb where station_name ='公交五公司' and line_id=a.line_id)
end短消息不能用了