用于从所选记录中选择最大数据的SQL命令。 [英] SQL command to select maximum data from selected record.

问题描述

大家好，



我有三张桌子：

桌子



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.

推荐答案

试试这段代码。

Try this code.

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命令。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！