数据库中有这样的一种数据,在网页中添加数据,添加某人的姓名和日期,添加一次算一次旷工,如果旷工一天则颜色不变,如果连续旷工两天则显示红色,如果连续旷工三天则显示绿色,如果连续旷工三天以上则显示黄色,要怎样编写。大家注意是连续的旷工才这样的。
姓名 旷工日期
张三 2011-9-14
张三 2011-9-15
张三 2011-9-16
李四 2011-9-10
李四 2011-9-12
李四 2011-9-14
张三 2011-9-20
response.Write "<table border=1>"
sql="select * from utable order by username,date "
set rs=server.CreateObject("adodb.recordset")
rs.open sql,conn,1,3
do while not rs.eof
response.Write "<tr>"
response.Write "<td>"&rs("ID")&"</td><td>"&rs("username")&"</td><td bgcolor='"&sub_utable(rs("username"),rs("date"))&"'>"&rs("date")&"</td>"
response.Write "</tr>"
rs.movenext
loop
response.Write "</table>"
function sub_utable(us,da)
flag=0
sub_utable=""
sql1="select * from utable where username='"&us&"' and datediff('d','"&da&"',[date])=3 order by date "
set rs1=server.CreateObject("adodb.recordset")
rs1.open sql1,conn,1,1
if rs1.recordcount>0 then flag=flag+1 else flag=0
rs1.close:set rs1=nothing
sql1="select * from utable where username='"&us&"' and datediff('d','"&da&"',[date])=2 order by date "
set rs1=server.CreateObject("adodb.recordset")
rs1.open sql1,conn,1,1
if rs1.recordcount>0 then flag=flag+1 else flag=0
rs1.close:set rs1=nothing
sql1="select * from utable where username='"&us&"' and datediff('d','"&da&"',[date])=1 order by date "
set rs1=server.CreateObject("adodb.recordset")
rs1.open sql1,conn,1,1
if rs1.recordcount>0 then flag=flag+1 else flag=0
rs1.close:set rs1=nothing
if flag>=1 then sub_utable="red"
if flag>=2 then sub_utable="green"
if flag>=3 then sub_utable="yellow":exit function
sql1="select * from utable where username='"&us&"' and datediff('d','"&da&"',[date])=-1 order by date "
set rs1=server.CreateObject("adodb.recordset")
rs1.open sql1,conn,1,1
if rs1.recordcount>0 then flag=flag+1 else flag=0
rs1.close:set rs1=nothing
if flag>=1 then sub_utable="red"
if flag>=2 then sub_utable="green"
if flag>=3 then sub_utable="yellow":exit function
sql1="select * from utable where username='"&us&"' and datediff('d','"&da&"',[date])=-2 order by date "
set rs1=server.CreateObject("adodb.recordset")
rs1.open sql1,conn,1,1
if rs1.recordcount>0 then flag=flag+1 else flag=0
rs1.close:set rs1=nothing
if flag>=2 then sub_utable="green"
if flag>=3 then sub_utable="yellow":exit function
sql1="select * from utable where username='"&us&"' and datediff('d','"&da&"',[date])=-3 order by date "
set rs1=server.CreateObject("adodb.recordset")
rs1.open sql1,conn,1,1
if rs1.recordcount>0 then flag=flag+1 else flag=0
rs1.close:set rs1=nothing
if flag>=3 then sub_utable="yellow":exit function
end function