现有一数据表格式如下
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
解决方案 »
- 不同的类可以实现同一个接口吗?实现的签名必须相同,但内容可以不同吗?
- 我在form里面有一个treeview,怎么样在拖动form大小的时候treeview也按比例变化?
- 如何让我的首页导航动态实现
- [!!!Help!!!]TcpClient与服务端通讯的问题[!!!急!!!]
- 求分页代码!
- 服务程序的问题
- 请问:怎样把DATASET里的表,原样存储到SQL数据库里,
- 哪位能给这个ADO.NET的SQL Server引擎把事物控制和数据库更新,添加上去或修改!100分!
- 让Form值实时的改变
- C# dataGridView 读取Excel 表格内容 报错:对象不能从DBNUll转换为其他类型
- C#连接SQL Server
- 大写问题 C#
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)