现有一数据表格式如下
ParentID DataRange ……
P001 0:10
P001 10:20
P001 20:30
P001 30:40
P001 40:50
P001 50:60
P001 60:70
P001 80:90
P002 0:15
P002 15:20
P002 20:35
P002 35:50
P002 50:60
P002 60:70
P002 70:80
P002 80:90输入参数为ParentID(例如P001)以及DataValue(例如15),如何根据ParentID及DataValue所匹配DataRange区间,唯一的返回一条记录?请各位不吝赐教?数据示例:假如输入参数ParentID = P001,DataValue = 15
则应该返回记录
ParentID DataRange ……
P001 10:20
ParentID DataRange ……
P001 0:10
P001 10:20
P001 20:30
P001 30:40
P001 40:50
P001 50:60
P001 60:70
P001 80:90
P002 0:15
P002 15:20
P002 20:35
P002 35:50
P002 50:60
P002 60:70
P002 70:80
P002 80:90输入参数为ParentID(例如P001)以及DataValue(例如15),如何根据ParentID及DataValue所匹配DataRange区间,唯一的返回一条记录?请各位不吝赐教?数据示例:假如输入参数ParentID = P001,DataValue = 15
则应该返回记录
ParentID DataRange ……
P001 10:20
FROM #T
WHERE ParentID = 'P001'
AND 15 BETWEEN
CAST(SUBSTRING(DataRange, 0, PATINDEX('%:%', DataRange)) AS INT)
AND
CAST(SUBSTRING(DataRange, PATINDEX('%:%', DataRange) + 1, LEN(DataRange)) AS INT)
--CHARINDEX
SELECT *
FROM #T
WHERE ParentID = 'P001'
AND 15 BETWEEN
CAST(SUBSTRING(DataRange, 0, CHARINDEX(':', DataRange)) AS INT)
AND
CAST(SUBSTRING(DataRange, CHARINDEX(':', DataRange) + 1, LEN(DataRange)) AS INT)
好~~鼓掌~~~
自己还真没用过#_#!! 俺的SQL用的烂啊 结贴去鸟
(
ParentID varchar(10) default '',
DataRange varchar(10) default ''
)insert into pd(ParentID,DataRange) values('P001','0:10')
insert into pd(ParentID,DataRange) values('P001','10:20')
insert into pd(ParentID,DataRange) values('P001','20:30')
insert into pd(ParentID,DataRange) values('P001','30:40')
insert into pd(ParentID,DataRange) values('P001','40:50')
insert into pd(ParentID,DataRange) values('P001','50:60')
insert into pd(ParentID,DataRange) values('P001','60:70')
insert into pd(ParentID,DataRange) values('P001','70:80')
insert into pd(ParentID,DataRange) values('P002','0:15')
insert into pd(ParentID,DataRange) values('P002','15:20')
insert into pd(ParentID,DataRange) values('P002','20:35')
insert into pd(ParentID,DataRange) values('P002','35:50')
insert into pd(ParentID,DataRange) values('P002','50:60')
insert into pd(ParentID,DataRange) values('P002','60:70')
insert into pd(ParentID,DataRange) values('P002','70:80')
insert into pd(ParentID,DataRange) values('P002','80:90')
SELECT *
FROM PD
WHERE ParentID = 'P001'
AND 40 BETWEEN
CAST(SUBSTRING(DataRange, 0, PATINDEX('%:%', DataRange)) AS INT)
AND
CAST(SUBSTRING(DataRange, PATINDEX('%:%', DataRange) + 1, LEN(DataRange)) AS INT)
AND CAST(SUBSTRING(DataRange, 0, PATINDEX('%:%', DataRange)) AS INT)=40以上查询结果:
P001 40:50
========================================================
SELECT *
FROM PD
WHERE ParentID = 'P001'
AND 40 BETWEEN
CAST(SUBSTRING(DataRange, 0, PATINDEX('%:%', DataRange)) AS INT)
AND
CAST(SUBSTRING(DataRange, PATINDEX('%:%', DataRange) + 1, LEN(DataRange)) AS INT)
AND CAST(SUBSTRING(DataRange, PATINDEX('%:%', DataRange) + 1, LEN(DataRange)) AS INT)=40
以上代码查询结果:
P001 30:40
对于这种边界的处理直接使用运算符处理即可。
SELECT *
FROM #T
WHERE ParentID = 'P001'
AND 15 >= -- 界值处理
CAST(SUBSTRING(DataRange, 0, CHARINDEX(':', DataRange)) AS INT)
AND < -- 界值处理
CAST(SUBSTRING(DataRange, CHARINDEX(':', DataRange) + 1, LEN(DataRange)) AS INT)