一个ID卡号频繁更换人如何接考勤记录
卡号 换卡日期 姓名
-----------------------
001 2013-01-01 张三
001 2013-01-05 李四
001 2013-01-10 王五
001 2013-01-17 赵六
现在从TXT里提取数据,得到卡号"001"与日期"2013-01-07",我如何查询接到这笔数据是李四的
[解决办法]
----------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-03-25 17:15:07
-- Version:
-- Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64)
--Jun 17 2011 00:54:03
--Copyright (c) Microsoft Corporation
--Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1, v.721)
--
----------------------------
--> 测试数据：[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([卡号] varchar(3),[换卡日期] datetime,[姓名] varchar(4))
insert [huang]
select '001','2013-01-01','张三' union all
select '001','2013-01-05','李四' union all
select '001','2013-01-10','王五' union all
select '001','2013-01-17','赵六'
--------------开始查询--------------------------
SELECT TOP 1
*
FROM huang
WHERE [卡号] = '001' AND ABS(DATEDIFF(dd, '2013-01-07', [换卡日期])) <= ( SELECT MIN(ABS(DATEDIFF(dd,
'2013-01-07',
[换卡日期]))) ddiff
FROM huang
WHERE [卡号] = '001'
)
----------------结果----------------------------
/*
卡号 换卡日期 姓名
---- ----------------------- ----
001 2013-01-05 00:00:00.000 李四
*/
