现在数据:
卡钟 工号 卡号 实际刷卡时间
ID clockNO emp_id card_id cdatetime
1 04 08597 0013717308 2011-03-02 00:00:43.000
2 04 08597 0013717308 2011-03-02 00:00:44.000
3 04 10305 0000257840 2011-03-02 02:00:06.000
4 04 10305 0000257840 2011-03-02 02:00:07.000
5 04 11127 0016202913 2011-03-02 02:56:04.000
6 04 11127 0016202913 2011-03-02 02:56:08.000
7 04 11127 0016202913 2011-03-02 02:56:13.000
8 04 08069 0016216517 2011-03-02 05:01:22.000
9 04 08069 0016216517 2011-03-02 05:01:23.000
10 04 08069 0016216517 2011-03-02 05:01:24.000
11 01 10188 1879218323 2011-03-02 05:04:35.000
12 01 10188 1879218323 2011-03-02 05:04:37.000想得到的数据是:
ID clockNO emp_id card_id cdatetime
2 04 08597 0013717308 2011-03-02 00:00:44.000
4 04 10305 0000257840 2011-03-02 02:00:07.000
7 04 11127 0016202913 2011-03-02 02:56:13.000
10 04 08069 0016216517 2011-03-02 05:01:24.000
12 01 10188 1879218323 2011-03-02 05:04:37.000
由于刚学习,需各位大侠请教,谢谢。
卡钟 工号 卡号 实际刷卡时间
ID clockNO emp_id card_id cdatetime
1 04 08597 0013717308 2011-03-02 00:00:43.000
2 04 08597 0013717308 2011-03-02 00:00:44.000
3 04 10305 0000257840 2011-03-02 02:00:06.000
4 04 10305 0000257840 2011-03-02 02:00:07.000
5 04 11127 0016202913 2011-03-02 02:56:04.000
6 04 11127 0016202913 2011-03-02 02:56:08.000
7 04 11127 0016202913 2011-03-02 02:56:13.000
8 04 08069 0016216517 2011-03-02 05:01:22.000
9 04 08069 0016216517 2011-03-02 05:01:23.000
10 04 08069 0016216517 2011-03-02 05:01:24.000
11 01 10188 1879218323 2011-03-02 05:04:35.000
12 01 10188 1879218323 2011-03-02 05:04:37.000想得到的数据是:
ID clockNO emp_id card_id cdatetime
2 04 08597 0013717308 2011-03-02 00:00:44.000
4 04 10305 0000257840 2011-03-02 02:00:07.000
7 04 11127 0016202913 2011-03-02 02:56:13.000
10 04 08069 0016216517 2011-03-02 05:01:24.000
12 01 10188 1879218323 2011-03-02 05:04:37.000
由于刚学习,需各位大侠请教,谢谢。
from tb t
where not exists (select 1 from tb where clockNO = t.clockNO and emp_id = t.emp_id
and card_id = t.card_id and cdatetime > t.cdatetime)
(select 1 from tb
where clockNO=a.clockNO and emp_id =a.emp_id and card_id=a.card_id
and cdatetime<a.cdatetime)