本文介绍了用于从所选记录中选择最大数据的SQL命令。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

大家好,



我有三张桌子:

桌子



1.检查:

i)EMPID

ii)CHECKTIME

2.EMP:

i)EMPID



3.TIMECARD:

i)TimeOUT



注意:CHECKINOUT(EMPID )= EMP(EMPID)



CHECKINOUT所有打卡时间都来自CHECKTIME栏目。



所以我想从chechinout表中选择所有EMPID(存在于EMP表中)一天(例如:2013年2月2日)。

之后我想选择最后一个打击给定日期的每位员工的列CHECKTIME打卡时间。

然后我想将最后一次打卡时间插入表TIMECARD列名超时。



如果有任何疑问请注释...评论。

Hi all,

I have three table :
TABLE

1. CHECKINOUT:
i)EMPID
ii)CHECKTIME
2.EMP:
i)EMPID

3.TIMECARD:
i)TimeOUT

Note : CHECKINOUT(EMPID)=EMP(EMPID)

CHECKINOUT where all the punch time will come and sit in Column CHECKTIME.

So i want to select the all EMPID(Which is present in EMP Table)from chechinout table For one day(eg: 2/2/2013).
After this i Want to select the Last punch time from Column CHECKTIME of every employee who punched for the given date.
Then i want to insert the last punch time to table TIMECARD Column name Timeout.

If any doubt plz ...comment.

推荐答案

declare @CHECKINOUT table  
(
EMPID int,
CHECKTIME datetime
)

declare @EMP table
(
EMPID int
)
--select * from @EMP;
declare @TIMECARD  table
(
EMPID int,
outTime datetime
)

insert into @EMP values(1)
insert into @EMP values(2)
insert into @EMP values(3)

insert into @CHECKINOUT values (1,'10-07-2013 10:00 am')
insert into @CHECKINOUT values (1,'10-07-2013 05:00 pm')
insert into @CHECKINOUT values (2,'10-07-2013 10:00 am')
insert into @CHECKINOUT values (2,'10-07-2013 05:30 pm')
insert into @CHECKINOUT values (1,'11-07-2013 10:00 am')
insert into @CHECKINOUT values (1,'11-07-2013 03:00 pm')
insert into @CHECKINOUT values (3,'11-07-2013 09:00 am')
insert into @CHECKINOUT values (3,'11-07-2013 05:30 pm')

select c.* from @CHECKINOUT c inner join @EMP e on e.EMPID=c.EMPID
Where convert(date,CHECKTIME,112) = '10-07-2013'

insert into @TIMECARD 
select c.EMPID,max(c.CHECKTIME )  
from @CHECKINOUT c inner join @EMP e on e.EMPID=c.EMPID
Where convert(date,CHECKTIME,112) = '10-07-2013'
group by c.EMPID 
select * from @TIMECARD 


这篇关于用于从所选记录中选择最大数据的SQL命令。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-30 04:24