[SQL]请帮忙分析一SQL语句写法，详见帖子

[jiatong1981]

现有一数据表格式如下
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

[骑蚊子旅游]

[Quote=引用 4 楼 pgy8288 的回复:]
SELECT *
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)
[/Quote]

这个是正解

另外若要处理边界问题的话其实就用上面的代码可以完成。
比如要查询40这个数字，会出现两条记录，若要查询出30:40的话，加个限制条件，限制最大值等于40的即可
AND CAST(SUBSTRING(DataRange, PATINDEX('%:%', DataRange) + 1, LEN(DataRange)) AS INT) = 40
或要查询出40:50的话，则为CAST(SUBSTRING(DataRange, 0, PATINDEX('%:%', DataRange)) AS INT) = 40

4楼很强，呵呵。帮顶，学习，收藏

[骑蚊子旅游]

我刚做了测试，所以顺便把相关T-SQL代码列出来供大家阅读者测试学习用，呵，借花献个佛

create table pd
(
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

[骑蚊子旅游]

这么早结贴了，俺一分都没，呵，苦闷的人喽

[jiatong1981]

感谢ls的热心
对于这种边界的处理

直接使用运算符处理即可。

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) 

[骑蚊子旅游]

嗯

[jiatong1981]

至于表结构的合理性问题，请各位不予讨论，就以此表结构为准。

[whoami333]

我只会笨办法，就不献丑了。帮你顶。

[jiatong1981]

2楼别走啊 知道啥都写出来……

[pgy8288]

SELECT *
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)

[pgy8288]

--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)

[fuda_1985]

顶顶

[zlz_212]

边界准备如何处理呢？

[jiatong1981]

CharIndex
好~~鼓掌~~~


自己还真没用过#_#!! 俺的SQL用的烂啊

结贴去鸟

[jiatong1981]

[Quote=引用 7 楼 zlz_212 的回复:]
边界准备如何处理呢？
[/Quote]

不知兄台的边界是指？

[stning]

up

[hhg_15]

学习..帮顶. .呵呵. !