如何使用ID查找缺少的信息 [英] How to find the missing information with ID

问题描述

我有桌子日志包含三栏：

<前郎=文字> EMPID
检查时间
SENSORID

当员工用RFID技工展示卡片时。

数据将会出现在桌子上，如

 EMPID ** *检查时间**** SENSORID 
 1234 *** 2010-06-30 07:31 **** 1 
 253 *** 2010-06-30 08:31 **** 2 
 
 1234 *** 2010-06-30 18:31 **** 3 

SENSORID 是RFID机器，例如：1,2,3



SENSORID 1和2适用于登录员工可以使用任何一个登录和 SENSORID 3用于注销，所以我想找到所有 EMPID 其登录信息是在表中但没有注销信息，因为 SENSORID 3不存在。

解决方案

以下语句应该给出从未退出的所有 EMPID ：

  SELECT   DISTINCT  EMPID，Checktime 
  FROM  Log 
  WHERE （SENSORID =  1  或 SENSORID =  2 ）
  AND  EMPID  NOT   IN （ SELECT   DISTINCT  EMPID 
  FROM  EMPID 
  WHERE  SENSORID =  3 ）

如果你想要 EMPID 's谁没有在登录使用的同一天退出：

  SELECT   DISTINCT  LogIn.EMPID，Login.Checktime 
  FROM 日志 AS 登录
  WHERE （登录。 SENSORID =  1  或 LogIn.SENSORID =  2 ）
  AND  LogIn.EMPID  NOT   IN （ SELECT   DISTINCT  LogOut.EMPID 
  FROM  EMPID  AS  LogOut 
  WHERE  LogOut.SENSORID =  3  
  AND  DATEDIFF（day，LogIn.Checktime，LogOut.Checktime）=  0 ）

上述语句假设 Checktime 存储为日期时间 [ ^ ]。



参考：日期 [ ^ ]

试试这个...

 < span class =code-keyword>选择 * 从 TableName t 
 其中 不 存在（选择 t2.EmpId  From  TableName t2 其中 t2.SensorId = 3 和 t2.EmpId = t.EmpId）

如果你想要没有注销的记录在那个特定的日子......

 选择 * 来自 #Temp t 
 其中 不 存在（选择 t2.EmpId 来自 #Temp t2 其中 t2.SensorId = 3 和 t2.EmpId = t.EmpId 和 DATEDIFF（D，t.CheckTime，t2.CheckTime）= 0）和 t.SensorId<> 3 

经过测试......

<前lang =sql> 创建 table #Temp（EmpId Int ，CheckTime DateTime ，SensorId Int ）
将 插入 #Temp
< span class =code-keyword>选择 ' 1234'， 2010-06-30 07:31'， 1 联盟 全部
选择 < span class =code-string>' 253'，' 2010-06-30 08:31'， 2 联盟 all
选择 ' < span class =code-string> 1234'，' 2010-06-30 18:31' ， 3

选择 * From #Temp t
wher e 不 存在（选择 t2.EmpId From #Temp t2 其中 t2.SensorId = 3 和 t2.EmpId = t.EmpId）

Drop 表 #Temp

输出：

 EmpId CheckTime SensorId 
 -----  - --------- --------- 
 253 2010-06-30 08：31：00.000 2 

I have table Log contain Three column they are:

EMPID 
Checktime
SENSORID

When employee show the card in RFID mechanic.
The data will come and sit in table like

EMPID ***     Checktime         ****  SENSORID
1234  ***     2010-06-30 07:31  ****    1
 253  ***     2010-06-30 08:31  ****    2

1234  ***     2010-06-30 18:31  ****    3

SENSORID is RFID machine eg: 1,2,3

SENSORID 1 and 2 is for login employee can use any one to login and SENSORID 3 is for logout, so I want to find all EMPID whose login information is in table but logout information is not there because SENSORID 3 is not there.

解决方案

The following statement should give all EMPID's who never logged out:

SELECT DISTINCT EMPID, Checktime
  FROM Log
 WHERE (SENSORID = 1 OR SENSORID = 2)
   AND EMPID NOT IN (SELECT DISTINCT EMPID
                       FROM EMPID
                      WHERE SENSORID = 3)

If you want the EMPID's who did not logout on the same day as they logged in use:

SELECT DISTINCT LogIn.EMPID, Login.Checktime
  FROM Log AS LogIn
 WHERE (LogIn.SENSORID = 1 OR LogIn.SENSORID = 2)
   AND LogIn.EMPID NOT IN (SELECT DISTINCT LogOut.EMPID
                             FROM EMPID AS LogOut
                            WHERE LogOut.SENSORID = 3
                              AND DATEDIFF(day, LogIn.Checktime, LogOut.Checktime) = 0)

Above statement assumes that Checktime is stored as DateTime[^] in the table.

Reference: DATEDIFF[^]

Try this...

Select * From TableName t 
where not exists (Select t2.EmpId From TableName t2 where t2.SensorId =3 and t2.EmpId=t.EmpId)

If you want the Records who has not log out on that particular day...

Select * From #Temp t
where not exists (Select t2.EmpId From #Temp t2 where t2.SensorId =3 and t2.EmpId=t.EmpId and DATEDIFF(D,t.CheckTime,t2.CheckTime)=0) and t.SensorId <>3

Tested...

Create table #Temp(EmpId Int, CheckTime DateTime, SensorId Int)
Insert into #Temp
Select '1234','2010-06-30 07:31',1 Union all
Select '253','2010-06-30 08:31',2 Union all
Select '1234','2010-06-30 18:31',3

Select * From #Temp t
where not exists (Select t2.EmpId From #Temp t2 where t2.SensorId =3 and t2.EmpId=t.EmpId)

Drop Table #Temp

Output:

EmpId	CheckTime	             SensorId
-----   -----------                  ---------
253	2010-06-30 08:31:00.000	        2

这篇关于如何使用ID查找缺少的信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！