打卡程序,求SQL

三散人 2011-02-16 03:36:12

打卡记录表 : CheckCardLog

LogID uid CheckTime
1 6 2011-01-01 17:31:55
2 6 2011-01-01 17:31:56
3 2 2011-01-01 17:33:55
4 3 2011-01-01 17:33:55
5 3 2011-01-01 17:35:21
6 3 2011-01-01 17:35:58
7 5 2011-01-01 17:39:50
8 5 2011-01-01 17:39:58

下班打卡,可多次,取后一次

要求查出每人(uid)下班时间
...全文
190 17 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
三散人 2011-02-16
  • 打赏
  • 举报
回复
结贴,给分,谢谢SSP2009
快溜 2011-02-16
  • 打赏
  • 举报
回复

select * from tb a where LogID in
(select top 1 LogID from tb
where Uid=a.Uid and GuestID=a.GuestID order by CreateDay desc)

chuanzhang5687 2011-02-16
  • 打赏
  • 举报
回复

select uid,max(checktime),card from CheckCardLog group by uid,card

三散人 2011-02-16
  • 打赏
  • 举报
回复
表没设计好,郁闷都要死啊,
三散人 2011-02-16
  • 打赏
  • 举报
回复
哭,感谢大家,

如果是这个表呢?该怎么写SQL

这个表是合约表,一个客户可以有多个合约(续约),

我要查询 每个客户有效的合约(取最后一次创建时间)

编号 我方签约人 客户ID 开始日期 结束日期 创建日期 备注
LogID Uid GuestID StartDay EndDay CreateDay Remark
O爱咋咋地O 2011-02-16
  • 打赏
  • 举报
回复
[Quote=引用 9 楼 qianjin036a 的回复:]
如果有多个工作日,则:

SQL code
create table CheckCardLog(LogID int,uid int,CheckTime datetime,Card nvarchar(5))
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55','一代卡'
insert into CheckCardLog sel……
[/Quote]
这个好
Iovswety 2011-02-16
  • 打赏
  • 举报
回复
create table CheckCardLog(LogID int,uid int,CheckTime datetime,Card nvarchar(5))
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55','一代卡'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56','一代卡'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21','一代卡'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58','一代卡'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50','二代卡'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58','二代卡'
go
select * from CheckCardLog A
where not exists (select 1 from CheckCardLog B where A.uid = B.uid and A.checktime < B.checktime)
go
drop table CheckCardLog
Q315054403 2011-02-16
  • 打赏
  • 举报
回复
要从结构设计解决,比如补卡、班次等。。。非一SQL之力
-晴天 2011-02-16
  • 打赏
  • 举报
回复
如果有多个工作日,则:
create table CheckCardLog(LogID int,uid int,CheckTime datetime,Card nvarchar(5))
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55','一代卡'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56','一代卡'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21','一代卡'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58','一代卡'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50','二代卡'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58','二代卡'
insert into CheckCardLog select 9,6,'2011-01-02 17:31:55','一代卡'
insert into CheckCardLog select 10,6,'2011-01-02 17:31:56','一代卡'
insert into CheckCardLog select 11,2,'2011-01-02 17:33:55','一代卡'
insert into CheckCardLog select 12,3,'2011-01-02 17:33:55','一代卡'
insert into CheckCardLog select 13,3,'2011-01-02 17:35:21','一代卡'
insert into CheckCardLog select 14,3,'2011-01-02 17:35:58','一代卡'
insert into CheckCardLog select 15,5,'2011-01-02 17:39:50','二代卡'
insert into CheckCardLog select 16,5,'2011-01-02 17:39:58','二代卡'
go
select * from checkcardlog a
where not exists(select 1 from checkcardlog where uid=a.uid and checktime>a.checktime and convert(varchar(10),checktime,120)=convert(varchar(10),a.checktime,120))
go
drop table CheckCardLog
/*
LogID uid CheckTime Card
----------- ----------- ----------------------- -----
2 6 2011-01-01 17:31:56.000 一代卡
3 2 2011-01-01 17:33:55.000 一代卡
6 3 2011-01-01 17:35:58.000 一代卡
8 5 2011-01-01 17:39:58.000 二代卡
10 6 2011-01-02 17:31:56.000 一代卡
11 2 2011-01-02 17:33:55.000 一代卡
14 3 2011-01-02 17:35:58.000 一代卡
16 5 2011-01-02 17:39:58.000 二代卡

(8 行受影响)

*/
Spade_J 2011-02-16
  • 打赏
  • 举报
回复
select a.* from  CheckCardLog a,(select uid,max(CheckTime) as CheckTime from CheckCardLog group by uid) x where a.uid=x.uid and a.CheckTime = x.CheckTime
三散人 2011-02-16
  • 打赏
  • 举报
回复
我要列出表里 所有人下班时间,卡种,记录编号, 怎么弄哦
-晴天 2011-02-16
  • 打赏
  • 举报
回复
create table CheckCardLog(LogID int,uid int,CheckTime datetime,Card nvarchar(5))
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55','一代卡'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56','一代卡'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55','一代卡'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21','一代卡'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58','一代卡'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50','二代卡'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58','二代卡'
go
select uid,max(checktime),card from CheckCardLog group by uid,card
go
drop table CheckCardLog
/*
uid card
----------- ----------------------- -----
5 2011-01-01 17:39:58.000 二代卡
2 2011-01-01 17:33:55.000 一代卡
3 2011-01-01 17:35:58.000 一代卡
6 2011-01-01 17:31:56.000 一代卡

(4 行受影响)

*/
打一壶酱油 2011-02-16
  • 打赏
  • 举报
回复
-- 这样可以查询 没人每天的 下班时间
select uid,CONVERT(varchar(100),CheckTime, 23),max(CheckTime) as 下班时间
from tb
group by uid,CONVERT(varchar(100),CheckTime, 23):
-晴天 2011-02-16
  • 打赏
  • 举报
回复
create table CheckCardLog(LogID int,uid int,CheckTime datetime)
insert into CheckCardLog select 1,6,'2011-01-01 17:31:55'
insert into CheckCardLog select 2,6,'2011-01-01 17:31:56'
insert into CheckCardLog select 3,2,'2011-01-01 17:33:55'
insert into CheckCardLog select 4,3,'2011-01-01 17:33:55'
insert into CheckCardLog select 5,3,'2011-01-01 17:35:21'
insert into CheckCardLog select 6,3,'2011-01-01 17:35:58'
insert into CheckCardLog select 7,5,'2011-01-01 17:39:50'
insert into CheckCardLog select 8,5,'2011-01-01 17:39:58'
go
select uid,max(checktime) from CheckCardLog group by uid
go
drop table CheckCardLog
/*
uid
----------- -----------------------
2 2011-01-01 17:33:55.000
3 2011-01-01 17:35:58.000
5 2011-01-01 17:39:58.000
6 2011-01-01 17:31:56.000

(4 行受影响)

*/
三散人 2011-02-16
  • 打赏
  • 举报
回复
LogID uid CheckTime Card
1 6 2011-01-01 17:31:55 一代卡
2 6 2011-01-01 17:31:56 一代卡
3 2 2011-01-01 17:33:55 一代卡
4 3 2011-01-01 17:33:55 一代卡
5 3 2011-01-01 17:35:21 一代卡
6 3 2011-01-01 17:35:58 一代卡
7 5 2011-01-01 17:39:50 二代卡
8 5 2011-01-01 17:39:58 二代卡

我是要列出表里 所有的资料

select uid,max(CheckTime) as 下班时间 from tb group by uid
这个是不行的

rucypli 2011-02-16
  • 打赏
  • 举报
回复
select * from tb A
where not exits (select 1 from tb where B where A.checktime<B.checktime)
快溜 2011-02-16
  • 打赏
  • 举报
回复

select uid,max(CheckTime) as 下班时间 from tb group by uid

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧