SQL 查询问题，情况比较复杂，求支持
例如在数据库中有DoucmentID
1,HVOU00001,啊哟
2，HVOU00002，啊
3，HVOU00003，S
4，HVOU00004，5
5，HDXA00001，D
6，HDXA00002，A
7，HDXA00003，说的
8，HDXA00004，DS
9，HDXA00005，DSA
我在查询的时候输入参数@DocumentID = 'HDXA00001,HVOU00001-HVOU00003'
这个时候我要的结果是查出1,2,3,4
@DocumentID = 'HDXA00001,HDXA00003-HDXA00005,HVOU00002-HVOU00004'
此时的结果应该是2,3,4,5,7,8,9
请问这样的查询如何处理？ sql 数据库
[解决办法]
谁看懂题意了 我要拜师
[解决办法]
create table #tb(id int,DocumentID varchar(10),remarks varchar(10))
insert into #tb
select 1,'HVOU00001','啊哟'
union all select 2,'HVOU00002','啊'
union all select 3,'HVOU00003','S'
union all select 4,'HVOU00004','5'
union all select 5,'HDXA00001','D'
union all select 6,'HDXA00002','A'
union all select 7,'HDXA00003','说的'
union all select 8,'HDXA00004','DS'
union all select 9,'HDXA00005','DSA'
go
--查询输入参数
declare @DocumentID varchar(100)
set @DocumentID = 'HDXA00001,HDXA00003-HDXA00005,HVOU00002-HVOU00004'
select a.*
from #tb a
inner join
(
select case when CHARINDEX('-',[key])>0 then LEFT([key],CHARINDEX('-',[key])-1) else [key] end as fromkey
,case when CHARINDEX('-',[key])>0 then right([key],len([key])-CHARINDEX('-',[key])) else '' end tokey
from
(select SUBSTRING([key],number,CHARINDEX(',',[key]+',',number)-number) as [key]
from (select @DocumentID+',' as [key]) a, master..spt_values
where number >=1 and type='p'
and number<len([key]) and substring(','+[key],number,1)=','
)t
)b on a.DocumentID=b.fromkey or a.DocumentID between b.fromkey and b.tokey
order by id
drop table #tb
/*
idDocumentIDremarks
2HVOU00002啊
3HVOU00003S
4HVOU000045
5HDXA00001D
7HDXA00003说的
8HDXA00004DS
9HDXA00005DSA
*/
