不谈算法,我觉得楼主思路要改一下
首先你这是个数据库相关程序吧,那么如果你设计的数据库结构,连这样一个简单的查询SQL语句都实现不了,我觉得应该说设计是不成功的,至少是有问题的......
还是建议你修改数据库结构,如果不能修改,那么就在当前字段存取数据的结构上动动脑筋.....
表中的数据不是直接用来让用户看的,所以你的这种1-5,8-10这样的结构可以考虑改一下,以利于数据查询。比如你前面提到的用1和0的结构,这个要比你现在的结构好,还是看看你的示例数据:
01-05,10-14,18-23,27-30 转换为11111000011111100011111100011110这样存储,那么要实现你现在的查询就简单了:
select * from tb where xm='AA' and mid(rq,20,1)='1'
...........
可不可以生成一个临时表(或数组),根据日期序列给指定位置填充数值.然后检查某位置是否变化判断是否存在/
如1-5,10-14,18-23,27-30 转化成:
array a={1,1,1,1,1,0,0,0,0,1,1,1,1,1,1,0,0,0,1,1,1,1,1,1,0,0,0,1,1,1,1,0}
if a(20)=1 then f("张三",20)=true
xm srq erq
张三 1 5
张三 10 14
张三 18 23
张三 27 30
.
.
.
查询某个人某天是否在值班
select * from tb where xm='张三' and rq between srq and erq
查询某天哪些人在值班
select * from tb where rq between srq and erq
Function CardObjCheckIsAvai(CodeObj As String, rq As long ) As Boolean
ObjProjectCheckIsAvai = False
ITEMCODE2() As String
ssnn.Open "SELECT * FROM 表名 WHERE (xm = " & CodeObj & ")", cn_desc
Do While Not ssnn.EOF
ITEMCODE2 = Split(ssnn("rq"), "-")
For i = LBound(ITEMCODE2) To UBound(ITEMCODE2) - 1
if i = rq then
ObjProjectCheckIsAvai =true
exit do
endif
Next
ssnn.MoveNext
Loop
ssnn.Close
End Function
直线思维的代码:
Function GetValue(ByVal xm As String, ByVal rq As Long) As Boolean
Dim cn As connection
Dim rs As Recordset
Dim arr
Dim i As Long, j As Long
Dim b As Long, e As Long
'连接数据库
Set cn = New connection
'cn.open......
'返回记录集
Set rs = New Recordset
'rs.open "select * from tb where xm='" & xm & "'",cn......
arr = rs!xm
arr = Split(arr, ",")
For i = 0 To UBound(arr)
b = Split(arr(i), "-")(0)
e = Split(arr(i), "-")(1)
For j = b To e
If j = rq Then
GetValue = True
Exit Function
End If
Next j
Next i