select * from table a where not exists
(
select EnNo from AuctionSell where DT ('一堆时间上的条件') and EnNo=a.EnNo
group by EnNo
having count(1)>=4
)
(
select EnNo from AuctionSell where DT ('一堆时间上的条件') and EnNo=a.EnNo
group by EnNo
having count(1)>=4
)
解决方案 »
- datetime类型,like查询%a%也能查询到数据
- 執行作業時出錯
- IN语句在这种情况下的效率
- 求sql server 2005中文企业版32位下载地址
- 安装petshop 3.0 后运行出项'NT AUTHORITY\NETWORK SERVICE'
- 如何用select 选取不包含几个字段,如select 不要a列,不要b列,不要c列 from table 其余的字段都要,解决马上结贴
- 我做的系统使用ADODC访问SQL数据,但只有2K以上系统才能通过程序访问,98就不可以,请高手指教
- 英文sql server2000数据可以用中文么?
- 请教:这样算不算频繁地读取数据库
- 在sql server上创建登陆的数目有限制吗?
- 关于SQL2000升级到2005后出现乱码(怎么调整排列顺序都不行)
- 关于BOM表的设计
GO
CREATE TABLE #T1
(
EnNo int,
DT DATETIME
)
Insert into #T1
VALUES
(6,'2013-7-2 7:05'),
(6,'2013-7-2 12:05'),
(6,'2013-7-2 12:31'),
(6,'2013-7-2 14:05'),
(1,'2013-7-2 14:11'),
(2,'2013-7-2 14:12'),
(2,'2013-7-2 14:12'),
(6,'2013-7-2 14:14'),
(6,'2013-7-2 14:14'),
(1,'2013-7-2 14:15'),
(3,'2013-7-2 14:16'),
(3,'2013-7-2 14:16'),
(4,'2013-7-2 14:29'),
(4,'2013-7-2 14:29'),
(6,'2013-7-2 17:33');
with t as (select *,CONVERT(varchar(19),DT,120) s from #T1)
select * from t where
not(exists (select 1 from t t1 where LEFT(t1.s,10)=LEFT(t.s,10) and RIGHT(t1.s,8)<'08:10:00' and t1.EnNo=t.EnNo group by t1.EnNo having count(1)>=1)
and exists (select 1 from t t1 where LEFT(t1.s,10)=LEFT(t.s,10) and RIGHT(t1.s,8) between '11:30:00' and '13:29:59' and t1.EnNo=t.EnNo group by t1.EnNo having count(1)>=2)
and exists (select 1 from t t1 where LEFT(t1.s,10)=LEFT(t.s,10) and RIGHT(t1.s,8)>='17:30:00' and t1.EnNo=t.EnNo group by t1.EnNo having count(1)>=1)
)order by EnNo
select *
from [表名]
where not (right(DT,5)<'08:10' or
right(DT,5) between '11:30' and '13:30' or
right(DT,5)>'17:30')
这是设计问题,非SQL语句问题
where not(right(DT,8)between '01:00:00' and '08:00:00' or
right(DT,8)between '11:30:00' and '13:30:00' or
right(DT,8)between '17:30:00' and '23:30:00')
这是4楼改了的,这些事刷卡了,但是时间不对的,也就是迟到或者早退的,我还需要一个没刷的~
tiao.Value = 0 '进度初始值
tiao.Minimum = 0 '进度条最小值
tiao.Maximum = zhongzhi.Value.Day '进度条最大值
sqlConnection1.Open() '打开数据库
Dim dataAdapter As New SqlDataAdapter
Dim dst As New DataSet
Dim dt As New DataTable
Dim mon As String
Dim da As String
Label1.Text = ""
TextBox1.Text = ""
Dim b As Integer = KaoID.Text
Dim sql As String = "Truncate Table fukang" '清空数据库
Dim sql1 As String = "BULK INSERT fukang FROM 'C:\kaoqin\" & kaoqinJD.Text & ".TXT'WITH(FIELDTERMINATOR = '\t',ROWTERMINATOR = '\n' )" '导入数据
Dim sqlCmd As SqlCommand = New SqlCommand(sql, sqlConnection1)
Dim sqlCmd1 As SqlCommand = New SqlCommand(sql1, sqlConnection1)
sqlCmd.ExecuteNonQuery()
sqlCmd1.ExecuteNonQuery()
For a = qishi.Value.Day To zhongzhi.Value.Day '当月天数循环
tiao.Value = tiao.Value + 1
If qishi.Value.Month < 10 Then '判断月的值是否为单数
mon = "0" & qishi.Value.Month
Else
mon = qishi.Value.Month
End If
If a < 10 Then '判断日的值是否为单数
da = "0" & a
Else
da = a
End If Dim sql2 As String = "select count(*) from fukang where (DT>='" & qishi.Value.Year & "/" & mon & "/" & da & " 00:10'and DT<='" & qishi.Value.Year & "/" & mon & "/" & da & " 08:10')and EnNo='" & b & "'" '查询员工早上打卡时间
Dim sql3 As String = "select count(*) from fukang where (DT>='" & qishi.Value.Year & "/" & mon & "/" & da & " 11:20'and DT<='" & qishi.Value.Year & "/" & mon & "/" & da & " 13:40')and EnNo='" & b & "'" '查询员工中午打卡时间
Dim sql4 As String = "select count(*) from fukang where (DT>='" & qishi.Value.Year & "/" & mon & "/" & da & " 17:20'and DT<='" & qishi.Value.Year & "/" & mon & "/" & da & " 23:20')and EnNo='" & b & "'" '查询员工下班打卡时间
Dim sql5 As String = "select EnNo,DT from fukang where DT>='" & qishi.Text & "'and DT<='" & zhongzhi.Text & " 23:50' and EnNo='" & b & "'" '侧边表的数据 Dim sqlCmd2 As SqlCommand = New SqlCommand(sql2, sqlConnection1)
Dim sqlCmd3 As SqlCommand = New SqlCommand(sql3, sqlConnection1)
Dim sqlCmd4 As SqlCommand = New SqlCommand(sql4, sqlConnection1)
Dim sqlCmd5 As SqlCommand = New SqlCommand(sql5, sqlConnection1)
'如果早上打卡次数小于1且者中午打卡次数小于1且下班打卡次数小于1
If sqlCmd2.ExecuteScalar() < 1 And sqlCmd3.ExecuteScalar() < 1 And sqlCmd4.ExecuteScalar() < 1 Then
Label1.Text = Label1.Text & "员工" & b & " " & da & "号没有上班" & Environment.NewLine
Else
If sqlCmd2.ExecuteScalar() < 1 Then '如果早上打卡次数小于1
Label1.Text = Label1.Text & "员工" & b & " " & da & "号上班无打卡记录" & Environment.NewLine
End If
If sqlCmd3.ExecuteScalar() = 1 Then '如果中午打卡次数等于1
Label1.Text = Label1.Text & "员工" & b & " " & da & "号中午只打一次卡" & Environment.NewLine
ElseIf sqlCmd3.ExecuteScalar() < 1 Then '如果中午打卡次数小于1
Label1.Text = Label1.Text & "员工" & b & " " & da & "号中午无打卡记录" & Environment.NewLine
End If
If sqlCmd4.ExecuteScalar() < 1 Then '如果下午打卡次数小于1
Label1.Text = Label1.Text & "员工" & b & " " & da & "号下班无打卡记录" & Environment.NewLine
End If
End If
dataAdapter.SelectCommand = sqlCmd5
dataAdapter.Fill(dst, "info")
dt = dst.Tables("info")
Next a Dim sql6 As String = "select EnNo,DT from fukang where not(right(DT,8)between '01:00:00' and '08:00:00' or right(DT,8)between '11:30:00' and '13:30:00' or right(DT,8)between '17:30:00' and '23:30:00')and EnNo='" & b & "'and ((left(DT,10)between '" & qishi.Text & "' and '" & zhongzhi.Text & "'))"
Dim sqlCmd6 As SqlCommand = New SqlCommand(sql6, sqlConnection1)
Dim rd As SqlDataReader = sqlCmd6.ExecuteReader
Dim riQi, riQi1 As String
If rd.HasRows Then
While rd.Read
riQi = rd("EnNo") & ""
riQi1 = rd("DT") & ""
TextBox1.Text = TextBox1.Text + " " + riQi1 + Environment.NewLine
End While
End If
tiao.Visible = False
DGV.AutoGenerateColumns = True '自动创建列
DGV.DataSource = dt
sqlConnection1.Close()