求解重复就餐问题

tom2d3918 2010-05-01 01:04:01

有就餐系统，存在有人重复打卡的问题，使用SQL2000数据库系统；
对应表名为 jctable ，对应相关字段如下：
工号详细时间餐别
ID time cb
001 2010-01-01 12:02:02 中餐
001 2010-01-01 12:02:03 中餐
002 2010-01-01 12:02:12 中餐
003 2010-01-01 12:02:13 中餐
004 2010-01-01 12:02:22 中餐
001 2010-01-01 17:02:25 晚餐
002 2010-01-01 17:02:02 晚餐
003 2010-01-01 17:02:02 晚餐
004 2010-01-01 17:02:02 晚餐
005 2010-01-01 17:02:02 晚餐
005 2010-01-01 17:02:02 晚餐
要求：需对上表jctable中的餐别做一个update动作，对重复就餐的人员：
1..如果是12:00~13:00之间打的卡，则打卡时间最迟的那一笔其餐别更新为“中餐重复”；
2..如果是17:00~18:00之间打的卡，则打卡时间最迟的那一笔其餐别更新为“晚餐重复” ；
3.其他时间段如出现打卡记录，其餐别均更新为“X”；
4.注意那个时间格式；
5.所谓的重复打卡，是指：同一天同一个人同一个餐别有一笔以上记录。

...全文

118 8 打赏收藏转发到动态举报

写回复

用AI写文章

8 条回复

切换为时间正序

请发表友善的回复…

发表回复

lovezx1028 2010-05-03

打赏
举报

ding yixia

喜-喜 2010-05-01

打赏
举报

--------------------SQL Server数据格式化工具-------------------

---------------------------------------------------------------

-- DESIGNER :happycell188(喜喜)

--       QQ :584738179

-- Development Tool :Microsoft Visual C++ 6.0    C Language 

-- FUNCTION :CONVERT DATA TO T-SQL

---------------------------------------------------------------

-- Microsoft SQL Server  2005

-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]

---------------------------------------------------------------

---------------------------------------------------------------



--(测试环境：SQL Server 2000)



use test

go

if object_id('test.dbo.jctable') is not null drop table jctable

-- 创建数据表

create table jctable

(

ID char(4),

time datetime,

cb char(10)

)

go

--插入测试数据

insert into jctable select '001','2010-01-01 12:02:02','中餐'

union all select '001','2010-01-01 12:05:03','中餐'

union all select '002','2010-01-01 12:02:12','中餐'

union all select '003','2010-01-01 12:02:13','中餐'

union all select '004','2010-01-01 12:02:22','中餐'

union all select '001','2010-01-01 17:02:25','晚餐'

union all select '002','2010-01-01 17:02:02','晚餐'

union all select '003','2010-01-01 17:02:02','晚餐'

union all select '004','2010-01-01 17:02:02','晚餐'

union all select '005','2010-01-01 17:02:02','晚餐'

union all select '005','2010-01-01 18:02:02','晚餐'

union all select '005','2010-01-01 17:05:02','晚餐'

union all select '001','2010-01-02 12:02:03','中餐'

union all select '002','2010-01-02 12:03:12','中餐'

union all select '002','2010-01-02 12:05:12','中餐'

union all select '003','2010-01-02 12:02:13','中餐'

union all select '004','2010-01-02 12:02:22','中餐'

union all select '004','2010-01-02 13:02:22','中餐'

union all select '001','2010-01-02 17:02:25','晚餐'

union all select '002','2010-01-02 17:02:02','晚餐'

union all select '003','2010-01-02 17:02:02','晚餐'

union all select '004','2010-01-02 17:02:02','晚餐'

union all select '005','2010-01-02 17:02:02','晚餐'

union all select '005','2010-01-02 17:04:02','晚餐'

go

--代码实现



-->更新数据

update jctable

set cb=(case when convert(char(5),a.time,8) between '12:00' and '13:00' and idd=1 then '中餐'

when convert(char(5),a.time,8) between '12:00' and '13:00' and idd=2 then '中餐重复'

when convert(char(5),a.time,8) between '17:00' and '18:00' and idd=1 then '晚餐'

when convert(char(5),a.time,8) between '17:00' and '18:00' and idd=2 then '晚餐重复'

else 'X' end)

from jctable a inner join

(

select ID,time=min(time),cb,idd=1 from jctable

group by ID,convert(char(10),time,120),cb

union all

select *,idd=2 from jctable t

where not exists

(select * from

	(select ID,time=min(time),cb from jctable

	group by ID,convert(char(10),time,120),cb)tt

where ID=t.ID and time=t.time and cb=t.cb)

)b

on a.ID=b.ID and a.time=b.time



-->显示更新后数据

select * from jctable order by time

/*测试结果



ID time cb   

---------------------------------------------

001 	2010-01-01 12:02:02.000	中餐      

002 	2010-01-01 12:02:12.000	中餐      

003 	2010-01-01 12:02:13.000	中餐      

004 	2010-01-01 12:02:22.000	中餐      

001 	2010-01-01 12:05:03.000	中餐重复  

002 	2010-01-01 17:02:02.000	晚餐      

003 	2010-01-01 17:02:02.000	晚餐      

004 	2010-01-01 17:02:02.000	晚餐      

005 	2010-01-01 17:02:02.000	晚餐      

001 	2010-01-01 17:02:25.000	晚餐      

005 	2010-01-01 17:05:02.000	晚餐重复  

005 	2010-01-01 18:02:02.000	X         

001 	2010-01-02 12:02:03.000	中餐      

003 	2010-01-02 12:02:13.000	中餐      

004 	2010-01-02 12:02:22.000	中餐      

002 	2010-01-02 12:03:12.000	中餐      

002 	2010-01-02 12:05:12.000	中餐重复  

004 	2010-01-02 13:02:22.000	X         

002 	2010-01-02 17:02:02.000	晚餐      

003 	2010-01-02 17:02:02.000	晚餐      

004 	2010-01-02 17:02:02.000	晚餐      

005 	2010-01-02 17:02:02.000	晚餐      

001 	2010-01-02 17:02:25.000	晚餐      

005 	2010-01-02 17:04:02.000	晚餐重复  



(24 行受影响)

*/

lmdeo 2010-05-01

打赏
举报

如果某人没有重复打卡，中餐、晚餐只有一次打卡记录，最后餐别变成x吗？

tom2d3918 2010-05-01

打赏
举报

[Quote=引用 1 楼 wkc168 的回复:]
SQL code

--12:00~13:00之间打的卡:
update a set a.cb='中餐重复'
from jctable a
where time in(select max(time) from jctable where a.id=id and convert(varchar(20),time,114) between '12:00:00:000' and '13:……
[/Quote]

按照你的写法，会有一个bug：如查出max time有一笔为17:00:01，update之后，会造成每一天只要有时间为17:00:01都变成晚餐餐重复

SQL77 2010-05-01

打赏
举报

----------------------------------------------------------------

-- Author  :SQL77(只为思齐老)

-- Date    :2010-05-01 13:43:02

-- Version:

--      Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) 

--	Oct 14 2005 00:33:37 

--	Copyright (c) 1988-2005 Microsoft Corporation

--	Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

--

----------------------------------------------------------------

--> 测试数据：#tb

if object_id('tempdb.dbo.#tb') is not null drop table #tb

go 

create table #tb([ID] varchar(3),[time] datetime,[cb] varchar(20))

insert #tb

select '001','2010-01-01 12:02:02','中餐' union all

select '001','2010-01-01 12:02:03','中餐' union all

select '002','2010-01-01 12:02:12','中餐' union all

select '003','2010-01-01 12:02:13','中餐' union all

select '004','2010-01-01 12:02:22','中餐' union all

select '001','2010-01-01 17:02:25','晚餐' union all

select '002','2010-01-01 17:02:02','晚餐' union all

select '003','2010-01-01 17:02:02','晚餐' union all

select '004','2010-01-01 17:02:02','晚餐' union all

select '005','2010-01-01 17:02:02','晚餐' union all

select '005','2010-01-01 17:02:02','晚餐'

--------------开始查询--------------------------

--drop table #t

select * ,idd=identity(int,1,1) into #t from #tb

--select *,

--(select count(1) from #t where id=t.id and cb=t.cb and datepart(hh,time)=datepart(hh,t.time) and idd<=t.idd) num



update #t set cb=cb+'重复' from #t t 

where idd=(select max(idd) from #t

			 where id=t.id and cb=t.cb and datepart(hh,time)=datepart(hh,t.time))

and exists(select 1 from #t where id=t.id and cb=t.cb and datepart(hh,time)=datepart(hh,t.time) and idd<>t.idd)

  and (datepart(hh,time) between 12 and 13 or  datepart(hh,time) between 17 and 18 )



select * from #t

----------------结果----------------------------

/* 



(11 行受影响)



(11 行受影响)



(2 行受影响)

ID   time                    cb                   idd

---- ----------------------- -------------------- -----------

001  2010-01-01 12:02:02.000 中餐                   1

001  2010-01-01 12:02:03.000 中餐重复                 2

002  2010-01-01 12:02:12.000 中餐                   3

003  2010-01-01 12:02:13.000 中餐                   4

004  2010-01-01 12:02:22.000 中餐                   5

001  2010-01-01 17:02:25.000 晚餐                   6

002  2010-01-01 17:02:02.000 晚餐                   7

003  2010-01-01 17:02:02.000 晚餐                   8

004  2010-01-01 17:02:02.000 晚餐                   9

005  2010-01-01 17:02:02.000 晚餐                   10

005  2010-01-01 17:02:02.000 晚餐重复                 11



(11 行受影响)





ID   time                    cb                   idd

---- ----------------------- -------------------- -----------

001  2010-01-01 12:02:02.000 中餐                   1

001  2010-01-01 12:02:03.000 中餐重复                 2

002  2010-01-01 12:02:12.000 中餐                   3

003  2010-01-01 12:02:13.000 中餐                   4

004  2010-01-01 12:02:22.000 中餐                   5

001  2010-01-01 17:02:25.000 晚餐                   6

002  2010-01-01 17:02:02.000 晚餐                   7

003  2010-01-01 17:02:02.000 晚餐                   8

004  2010-01-01 17:02:02.000 晚餐                   9

005  2010-01-01 17:02:02.000 晚餐                   10

005  2010-01-01 17:02:02.000 晚餐重复                 11



(11 行受影响)





*/

--小F-- 2010-05-01

打赏
举报

没测试:

update 

  a

set

  [cb]=b.[cb]

from

  tb a,

  (select

     id,time,

     cb=case when convert(varchar(5) , [time], 108 ) between '12:00' and '13:00' then '中餐重复'

             when convert(varchar(5) , [time], 108 ) between '17:00' and '18:00' then '晚餐重复'

        else 'x' end

    from

      tb t

    where

      [time]=(select max([time]) from tb where id=t.id))b

where

   a.id=b.id

心中的彩虹 2010-05-01

打赏
举报



--12:00~13:00之间打的卡:

update a set a.cb='中餐重复'

from jctable a

where time in(select max(time) from  jctable  where a.id=id and convert(varchar(20),time,114) between '12:00:00:000' and '13:00:00:000' 

group by id

having count(*)>=2)



--17:00~18:00之间打的卡

update a set a.cb='晚餐重复'

from jctable a

where time in(select max(time) from  jctable  where a.id=id and convert(varchar(20),time,114) between '17:00:00:000' and '18:00:00:000' 

group by id

having count(*)>=2)

--其他时间段如出现打卡记录 

update a set a.cb='X'

from jctable a

where time in(select max(time) from  jctable  where a.id=id and convert(varchar(20),time,114) between '13:00:01:000' and '17:59:59:000' 

group by id

having count(*)>=2)

心中的彩虹 2010-05-01

打赏
举报



--12:00~13:00之间打的卡:

update a set a.cb='中餐重复'

from jctable a

where time in(select max(time) from  jctable  where a.id=id and convert(varchar(20),time,114) between '12:00:00:000' and '13:00:00:000' 

group by id

having count(*)>=2)



--17:00~18:00之间打的卡

update a set a.cb='中餐重复'

from jctable a

where time in(select max(time) from  jctable  where a.id=id and convert(varchar(20),time,114) between '17:00:00:000' and '18:00:00:000' 

group by id

having count(*)>=2)

--其他时间段如出现打卡记录 

update a set a.cb='中餐重复'

from jctable a

where time in(select max(time) from  jctable  where a.id=id and convert(varchar(20),time,114) between '13:00:01:000' and '17:59:59:000' 

group by id

having count(*)>=2)