有如下出勤表
ID DEPTCODE DATETIME
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-17 12:02:10.000
32 财务部 2009-10-17 13:07:19.000
32 财务部 2009-10-18 08:54:02.000
32 财务部 2009-10-18 12:02:10.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-17 12:02:10.000
50 市场部 2009-10-17 13:07:19.000
50 市场部 2009-10-18 08:54:02.000
50 市场部 2009-10-18 12:02:10.000
50 市场部 2009-10-18 13:07:19.000我想只保留每个ID当天最早和最晚的时间,中间的时间删掉,修改后如下
ID DEPTCODE DATETIME
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-17 13:07:19.000
32 财务部 2009-10-18 08:54:02.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-17 13:07:19.000
50 市场部 2009-10-18 08:54:02.000
50 市场部 2009-10-18 13:07:19.000
ID DEPTCODE DATETIME
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-17 12:02:10.000
32 财务部 2009-10-17 13:07:19.000
32 财务部 2009-10-18 08:54:02.000
32 财务部 2009-10-18 12:02:10.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-17 12:02:10.000
50 市场部 2009-10-17 13:07:19.000
50 市场部 2009-10-18 08:54:02.000
50 市场部 2009-10-18 12:02:10.000
50 市场部 2009-10-18 13:07:19.000我想只保留每个ID当天最早和最晚的时间,中间的时间删掉,修改后如下
ID DEPTCODE DATETIME
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-17 13:07:19.000
32 财务部 2009-10-18 08:54:02.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-17 13:07:19.000
50 市场部 2009-10-18 08:54:02.000
50 市场部 2009-10-18 13:07:19.000
select * from DataTable t
where DATETIME=(select Min(DATETIME) from DataTable where t.ID=ID)
or DATETIME=(select Max(DATETIME) from DataTable where t.ID=ID)
from tb
group by id,depcode,convert(nvarchar(10),datetime,120)
union all
select id,depcode,min(datetime)
from tb
group by id,depcode,convert(nvarchar(10),datetime,120)
order by id
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-29 18:10:05
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID int,DEPTCODE varchar(6),DATETIME datetime)
INSERT INTO @tb
SELECT 32,'财务部','2009-10-17 08:54:02.000' UNION ALL
SELECT 32,'财务部','2009-10-17 12:02:10.000' UNION ALL
SELECT 32,'财务部','2009-10-17 13:07:19.000' UNION ALL
SELECT 32,'财务部','2009-10-18 08:54:02.000' UNION ALL
SELECT 32,'财务部','2009-10-18 12:02:10.000' UNION ALL
SELECT 32,'财务部','2009-10-18 13:07:19.000' UNION ALL
SELECT 50,'市场部','2009-10-17 08:54:02.000' UNION ALL
SELECT 50,'市场部','2009-10-17 12:02:10.000' UNION ALL
SELECT 50,'市场部','2009-10-17 13:07:19.000' UNION ALL
SELECT 50,'市场部','2009-10-18 08:54:02.000' UNION ALL
SELECT 50,'市场部','2009-10-18 12:02:10.000' UNION ALL
SELECT 50,'市场部','2009-10-18 13:07:19.000'--SQL查询如下:--1.
DELETE A
FROM @tb AS A
LEFT JOIN (SELECT ID,MIN(DATETIME) AS minDate,MAX(DATETIME) AS maxDate
FROM @tb GROUP BY ID) AS B
ON A.ID = B.ID AND (A.DATETIME=B.minDate OR A.DATETIME=B.maxDate)
WHERE B.ID IS NULL;SELECT * FROM @tb;/*
ID DEPTCODE DATETIME
----------- -------- -----------------------
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-18 13:07:19.000(4 行受影响)
*/
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-10-29 18:10:05
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (ID int,DEPTCODE varchar(6),DATETIME datetime)
INSERT INTO @tb
SELECT 32,'财务部','2009-10-17 08:54:02.000' UNION ALL
SELECT 32,'财务部','2009-10-17 12:02:10.000' UNION ALL
SELECT 32,'财务部','2009-10-17 13:07:19.000' UNION ALL
SELECT 32,'财务部','2009-10-18 08:54:02.000' UNION ALL
SELECT 32,'财务部','2009-10-18 12:02:10.000' UNION ALL
SELECT 32,'财务部','2009-10-18 13:07:19.000' UNION ALL
SELECT 50,'市场部','2009-10-17 08:54:02.000' UNION ALL
SELECT 50,'市场部','2009-10-17 12:02:10.000' UNION ALL
SELECT 50,'市场部','2009-10-17 13:07:19.000' UNION ALL
SELECT 50,'市场部','2009-10-18 08:54:02.000' UNION ALL
SELECT 50,'市场部','2009-10-18 12:02:10.000' UNION ALL
SELECT 50,'市场部','2009-10-18 13:07:19.000'--SQL查询如下:--2.
DELETE A
FROM @tb AS A
LEFT JOIN (SELECT ID,MIN(DATETIME) AS minDate,MAX(DATETIME) AS maxDate
FROM @tb GROUP BY ID) AS B
ON A.ID = B.ID AND (A.DATETIME=B.minDate OR A.DATETIME=B.maxDate)
WHERE B.ID IS NOT NULL;SELECT * FROM @tb;/*
ID DEPTCODE DATETIME
----------- -------- -----------------------
32 财务部 2009-10-17 12:02:10.000
32 财务部 2009-10-17 13:07:19.000
32 财务部 2009-10-18 08:54:02.000
32 财务部 2009-10-18 12:02:10.000
50 市场部 2009-10-17 12:02:10.000
50 市场部 2009-10-17 13:07:19.000
50 市场部 2009-10-18 08:54:02.000
50 市场部 2009-10-18 12:02:10.000(8 行受影响)*/
select id,depcode,max([datetime])
from tb
group by id,depcode
union all
select id,depcode,min([datetime])
from tb
group by id,depcode
order by id
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([ID] int,[DEPTCODE] varchar(6),[DATETIME] datetime)
insert [TB]
select 32,'财务部','2009-10-17 08:54:02.000' union all
select 32,'财务部','2009-10-17 12:02:10.000' union all
select 32,'财务部','2009-10-17 13:07:19.000' union all
select 32,'财务部','2009-10-18 08:54:02.000' union all
select 32,'财务部','2009-10-18 12:02:10.000' union all
select 32,'财务部','2009-10-18 13:07:19.000' union all
select 50,'市场部','2009-10-17 08:54:02.000' union all
select 50,'市场部','2009-10-17 12:02:10.000' union all
select 50,'市场部','2009-10-17 13:07:19.000' union all
select 50,'市场部','2009-10-18 08:54:02.000' union all
select 50,'市场部','2009-10-18 12:02:10.000' union all
select 50,'市场部','2009-10-18 13:07:19.000'select * from TB t where
(select count(1)+1 from TB where t.DEPTCODE=DEPTCODE and convert(varchar(10),[DATETIME],120)=convert(varchar(10),t.[DATETIME],120)
and datename(hh,[DATETIME])>datename(hh,t.[DATETIME]))<>3
/*
ID DEPTCODE DATETIME
----------- -------- ------------------------------------------------------
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-17 13:07:19.000
32 财务部 2009-10-18 08:54:02.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-17 13:07:19.000
50 市场部 2009-10-18 08:54:02.000
50 市场部 2009-10-18 13:07:19.000(所影响的行数为 8 行)
*/drop table TB
--测试数据
if object_id('tb') is not null
drop table tb
create table tb (ID int,DEPTCODE nvarchar(30),datetime datetime )
insert into tb select 32 ,'财务部', '2009-10-17 08:54:02.000' union all
select 32 ,'财务部', '2009-10-17 12:02:10.000' union all
select 32, '财务部', '2009-10-17 13:07:19.000' union all
select 32, '财务部', '2009-10-18 08:54:02.000' union all
select 32, '财务部', '2009-10-18 12:02:10.000' union all
select 32 ,'财务部', '2009-10-18 13:07:19.000' union all
select 50 ,'市场部', '2009-10-17 08:54:02.000' union all
select 50 ,'市场部', '2009-10-17 12:02:10.000' union all
select 50 ,'市场部', '2009-10-17 13:07:19.000'union all
select 50 ,'市场部', '2009-10-18 08:54:02.000' union all
select 50 ,'市场部', '2009-10-18 12:02:10.000' union all
select 50 ,'市场部', '2009-10-18 13:07:19.000'
--删除记录
delete from tb where datetime
not in (select min(datetime) from tb t where t.id = id and
convert(char(10),datetime,120) = convert(char(10),t.datetime,120))
and datetime not in (select max(datetime) from tb t where t.id = id and
convert(char(10),datetime,120) = convert(char(10),t.datetime,120))
select * from tb
--结果
--------------------------------
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-18 13:07:19.000
insert into a
select
32,'财务部','2009-10-17 08:54:02.000' union all select
32,'财务部','2009-10-17 12:02:10.000' union all select
32,'财务部','2009-10-17 13:07:19.000' union all select
32,'财务部','2009-10-18 08:54:02.000' union all select
32,'财务部','2009-10-18 12:02:10.000' union all select
32,'财务部','2009-10-18 13:07:19.000' union all select
50,'市场部','2009-10-17 08:54:02.000' union all select
50,'市场部','2009-10-17 12:02:10.000' union all select
50,'市场部','2009-10-17 13:07:19.000' union all select
50,'市场部','2009-10-18 08:54:02.000' union all select
50,'市场部','2009-10-18 12:02:10.000' union all select
50,'市场部','2009-10-18 13:07:19.000';select * from a;
delete t1
from a as t1
where not exists(
select id, deptcode, min(datetimes) datetimes
from a group by id, deptcode
having min(datetimes)=t1.datetimes
union all
select id, deptcode, max(datetimes) datetimes
from a group by id, deptcode
having max(datetimes)=t1.datetimes )
--不好意思,上面的有点问题
if object_id('tb') is not null
drop table tb
create table tb (ID int,DEPTCODE nvarchar(30),datetime datetime )
insert into tb select 32 ,'财务部', '2009-10-17 08:54:02.000' union all
select 32 ,'财务部', '2009-10-17 12:02:10.000' union all
select 32, '财务部', '2009-10-17 13:07:19.000' union all
select 32, '财务部', '2009-10-18 08:54:02.000' union all
select 32, '财务部', '2009-10-18 12:02:10.000' union all
select 32 ,'财务部', '2009-10-18 13:07:19.000' union all
select 50 ,'市场部', '2009-10-17 08:54:02.000' union all
select 50 ,'市场部', '2009-10-17 12:02:10.000' union all
select 50 ,'市场部', '2009-10-17 13:07:19.000'union all
select 50 ,'市场部', '2009-10-18 08:54:02.000' union all
select 50 ,'市场部', '2009-10-18 12:02:10.000' union all
select 50 ,'市场部', '2009-10-18 13:07:19.000'
delete from tb where datetime
not in (select min(datetime) from tb t where t.id = id and
convert(char(10),datetime,120) = convert(char(10),t.datetime,120) group by convert(char(10),t.datetime,120),t.id)
and datetime not in (select max(datetime) from tb t where t.id = id and
convert(char(10),datetime,120) = convert(char(10),t.datetime,120) group by convert(char(10),t.datetime,120),t.id)
select * from tb
--结果
-----------------------------------------
32 财务部 2009-10-17 08:54:02.000
32 财务部 2009-10-17 13:07:19.000
32 财务部 2009-10-18 08:54:02.000
32 财务部 2009-10-18 13:07:19.000
50 市场部 2009-10-17 08:54:02.000
50 市场部 2009-10-17 13:07:19.000
50 市场部 2009-10-18 08:54:02.000
50 市场部 2009-10-18 13:07:19.000
drop table a;create table a(id int, deptcode varchar(20), datetimes datetime);
insert into a
select
32,'财务部','2009-10-17 08:54:02.000' union all select
32,'财务部','2009-10-17 12:02:10.000' union all select
32,'财务部','2009-10-17 13:07:19.000' union all select
32,'财务部','2009-10-18 08:54:02.000' union all select
32,'财务部','2009-10-18 12:02:10.000' union all select
32,'财务部','2009-10-18 13:07:19.000' union all select
50,'市场部','2009-10-17 08:54:02.000' union all select
50,'市场部','2009-10-17 12:02:10.000' union all select
50,'市场部','2009-10-17 13:07:19.000' union all select
50,'市场部','2009-10-18 08:54:02.000' union all select
50,'市场部','2009-10-18 12:02:10.000' union all select
50,'市场部','2009-10-18 13:07:19.000';select * from a;
delete t1
from a as t1
where not exists(
select id, deptcode, min(datetimes) datetimes
from a group by id, deptcode, convert(varchar(10),datetimes,120)
having min(datetimes)=t1.datetimes
union all
select id, deptcode, max(datetimes) datetimes
from a group by id, deptcode, convert(varchar(10),datetimes,120)
having max(datetimes)=t1.datetimes )select * from a;
FROM @tb AS A
LEFT JOIN (SELECT ID,CONVERT(varchar(10),DATETIME,120) AS DATETIME,
MIN(DATETIME) AS minDate,MAX(DATETIME) AS maxDate
FROM @tb GROUP BY ID,CONVERT(varchar(10),DATETIME,120)) AS B
ON A.ID = B.ID AND (A.DATETIME=B.minDate OR A.DATETIME=B.maxDate)
AND CONVERT(varchar(10),A.DATETIME,120) = B.DATETIME
WHERE B.ID IS NOT NULL;