我有一值勤表:
xm rq
张三 1-5,10-14,18-23,27-30
李四 6-9,15-17,24-26
王五 5-15,20-22
现在我如何判断某一天某人是否在值班?
如:给定函数f(xm,rq)
得f(张三,20)=true;
f(李四,20)=false;
特请教该函数如何写?
xm rq
张三 1-5,10-14,18-23,27-30
李四 6-9,15-17,24-26
王五 5-15,20-22
现在我如何判断某一天某人是否在值班?
如:给定函数f(xm,rq)
得f(张三,20)=true;
f(李四,20)=false;
特请教该函数如何写?
解决方案 »
- 文本文件列的判断
- datareport 的问题?????关于根据设定的条件来显示报表
- SQL Server版沉下去了,希望在VB版能顶起来,随便散散分。。。
- ********************vb有关介绍界面方面开发的电子书籍?********************
- 用printer来打印一张图片,怎么打?(最好不用其他控件,最好提供源代码,谢谢)
- DataGrid 的问题
- 一个菜鸟的苯问题,谢谢高手指导我这个可怜的菜鸟
- 报表!给分!!!
- 如何判断鼠标、键盘的动作
- 急急,关于asp,连接数据库的问题?
- vb excel汇出问题,急!!!!!!!!!!!!!!!!
- 续《ListView与TextBox的数据传送问题》
Function CardObjCheckIsAvai(CodeObj As String, rq As string) As Boolean
ObjProjectCheckIsAvai = False
For i = LBound(rq)To UBound(rq) - 1
ssnn.Open "SELECT * FROM TB_DRAWER WHERE (ITEMCODE = " & CodeObj & ")", cn_desc
Do While Not ssnn.EOF
if inst(ssnn("rq"),"rq") > 0
CardObjCheckIsAvai =true
exit do
endif
ssnn.MoveNext
Loop
ssnn.Close
Next
loop
ObjProjectCheckIsAvai = False ssnn.Open "SELECT * FROM 表明 WHERE (xm = " & CodeObj & ")", cn_desc
Do While Not ssnn.EOF
if inst(ssnn("rq"),"rq") > 0
CardObjCheckIsAvai =true
exit do
endif
ssnn.MoveNext
Loop
ssnn.Close
End Function
'你可以不用第二个参数,第二个参数从数据库中取得.'取得的SQL语句加是 "Where xm = '" & xm & "'"code=VB]Option ExplicitPrivate Sub Command1_Click()
MsgBox f("张三", "1-5,10-14,18-23,27-30", 25)
End Sub
Private Function f(ByVal xm As String, ByVal rqs As String, ByVal rq As Long) As Boolean
Dim Arr1() As String
Dim Arr2() As String
f = False
On Error GoTo Handler
Arr1 = Split(rqs, ",")
Dim i As Long
For i = 0 To UBound(Arr1)
Arr2 = Split(Arr1(i), "-")
If CLng(Arr2(0)) <= rq And rq <= CLng(Arr2(1)) Then
f = True
End If
Next
Exit Function
Handler:
f = False
End Function[/code]
并不能检查出20在字串18-23中.
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
End Function没测试......
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
李四 6-9,15-17,24-26
王五 5-15,20-22 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
如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
处理基础数据的时候可以这样1-5
2^5-2^(1-1)=(11111)2
6-10
2^10-2^(6-1)=1111100000然后把某个人所有值班数据转换后的值相加 活着用 or 运算也可以位运算比较快速且节省空间
首先你这是个数据库相关程序吧,那么如果你设计的数据库结构,连这样一个简单的查询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'
...........