如何插入正确匹配的数据 [英] How to insert the data with proper match

问题描述

我有一个表签入，它包括emp日志的所有事务

USERID
Chektime
sensorid

Hi,

I have one table checkinout it include all the transaction of emp log

USERID
Chektime
sensorid

USERID..Checktime.......sensorid
 
1	8/19/2013 08:19	I
2	8/19/2013 14:53	I
3	8/19/2013 13:19	I
2	8/19/2013 13:22	0
3	8/19/2013 14:52	0
2	8/19/2013 18:22	I
1	8/19/2013 16:21	0
1	8/19/2013 16:46	I
2	8/19/2013 16:45	I
1	8/19/2013 16:45	I

现在我还有一个表比较..
userID
签到
Checout

在此表中，我要插入所有带有sensorid =''I''
的记录
查询:

now i have one more table diff..
userID
checkin
Checout

In this table i want insert all the record with sensorid=''I''

query:

INSERT   INTO diff(CHECKIN)  SELECT checktime from checkinout where sensorid='I'

USERID..Checkin ........ checkout
1 8/19/2013 08:19
2 8/19/2013 14:53
3 8/19/2013 13:19
2 8/19/2013 18:22
1 8/19/2013 16:46
2 8/19/2013 16:45
1 8/19/2013 16:45

现在我想从签到表中插入签出，其中sensorid ="0"

所以我写查询:

USERID..Checkin........checkout
1 8/19/2013 08:19
2 8/19/2013 14:53
3 8/19/2013 13:19
2 8/19/2013 18:22
1 8/19/2013 16:46
2 8/19/2013 16:45
1 8/19/2013 16:45

Now i want to insert checkout from checkinout table where sensorid =''0''

so i write query:

INSERT   INTO diff(CHECKOUT)  SELECT checktime from checkinout where sensorid='0'

2 8/19/2013 13:22 0
3 8/19/2013 14:52 0
1 8/19/2013 16:21 0

但是问题是在checkout列中，datetime应该分别对应于USERID和checkin

假设
userid ..签到...签出
1 8/19/2013 08:19 8/19/2013 16:21
作为用户标识，第一个日志以"I"身份签入，第一个第二个日志以"0"身份签

请帮助...

2 8/19/2013 13:22 0
3 8/19/2013 14:52 0
1 8/19/2013 16:21 0

but the problem is that in checkout column the datetime should come respective to the USERID and checkin

suppose
userid.. checkin...... checkout
1 8/19/2013 08:19 8/19/2013 16:21
as userid first log as ''I'' is checkin and first second log as ''0''

plz help...

推荐答案

我想我有一个适合您的解决方案.

首先，我认为您的数据有点不连贯.您有用户在签入之前签出，并且用户两次签入.

如果我是对的，那么您希望有一对(USERID，签入时间)和最小的签出时间(如果有)在该签入时间之后.

使用正确的数据，我尝试了一下，它似乎可以工作:

I think I have a solution for you.

First, I think your data is a bit incoherent. You have users checking out before checking in and users checking in twice.

If I’m right, you want a pair (USERID, Checkin Time) and the smallest Checkout time, if any, after that Checkin time.

With the correct data I tried this and it seems to work:

SELECT A.USERID, A.Checktime AS Checkin, MIN(B.Checktime) AS CheckOUT
FROM  (SELECT USERID, Checktime, sensorid
       FROM   checkinout
       WHERE  sensorid = 'I') AS A 
LEFT OUTER JOIN
      (SELECT USERID, Checktime, sensorid
       FROM   checkinout AS checkinout_1
       WHERE  sensorid = '0') AS B 
ON A.USERID = B.USERID AND B.Checktime > A.Checktime
GROUP BY A.USERID, A.Checktime

在这种情况下，请不要忘记标记为已回答.

Don’t forget to mark as answered if it’s the case.

这篇关于如何插入正确匹配的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！