select * from
(select sum(yg), year(Time) y, month(Time) m from 表b
group by year(Time), month(Time)) a inner join (select sum(sales), year(Time) y , month(Time) m from 表a
group by year(Time), month(Time)) bon a.y=b.y and a.m=b.mdeclare @tmp table
(
name varchar(100),
mintime datetime,
maxtime datetime
)select name, min(时间), max(时间) into @tmp from table1
group by year(时间), month(时间), day(时间), namedelete from table1insert into table1
(name, 时间)
select name, mintime
from @tmpinsert into table1
(name, 时间)
select name, maxtime
from @tmp
(select sum(yg), year(Time) y, month(Time) m from 表b
group by year(Time), month(Time)) a inner join (select sum(sales), year(Time) y , month(Time) m from 表a
group by year(Time), month(Time)) bon a.y=b.y and a.m=b.mdeclare @tmp table
(
name varchar(100),
mintime datetime,
maxtime datetime
)select name, min(时间), max(时间) into @tmp from table1
group by year(时间), month(时间), day(时间), namedelete from table1insert into table1
(name, 时间)
select name, mintime
from @tmpinsert into table1
(name, 时间)
select name, maxtime
from @tmp
考勤记录中,不会存在倒班的情况的话,那就简单了很多!
delete from Table1
where right(convert(varchar(13),时间,120),) between '10' and '15'--剔除了早退和迟到的记录其实你这个问题,好像也可以想得复杂点
from table1 a
where exists (
select 1 from table1
where name=a.name
and convert(char(10),时间,120)=convert(char(10),a.时间,120)
and 时间<a.时间
)
and exists (
select 1 from table1
where name=a.name
and convert(char(10),时间,120)=convert(char(10),a.时间,120)
and 时间>a.时间
)
(
name varchar(100),
mintime datetime,
maxtime datetime
)select name, min(时间), max(时间) into @tmp from table1
group by year(时间), month(时间), day(时间), namedelete from table1insert into table1
(name, 时间)
select name, mintime
from @tmpinsert into table1
(name, 时间)
select name, maxtime
from @tmp
where 时间 <> (select min(时间) from Table1 group by name, convert(varchar,时间,111)) and
时间 <> (select max(时间) from Table1 group by name, convert(varchar,时间,111))
declare @t table(id int, name varchar(10), time datetime)
insert into @t
select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 10:00:00'
union all select 3, '李' , '2006-10-10 18:00:00'
union all select 4, '李' , '2006-10-11 09:00:00'
union all select 5, '李' , '2006-10-11 18:00:00'select max(time) from @t group by name, convert(varchar,time,111)
delete @t
from @t a
where time <> (select min(time) from @t where name = a.name and convert(varchar,time,111) = convert(varchar,a.time,111) group by name, convert(varchar,time,111))
and time <> (select max(time) from @t where name = a.name and convert(varchar,time,111) = convert(varchar,a.time,111) group by name, convert(varchar,time,111))select * from @t
Insert into table1
Select 1, '李', '2006-10-10 09:00:00' union all
Select 2, '李', '2006-10-10 10:00:00' union all
Select 3, '李', '2006-10-10 18:00:00' union all
Select 4, '李', '2006-10-11 09:00:00' union all
Select 5, '李', '2006-10-11 18:00:00'
--------------------删除---------------------------------------
Delete Table1 from table1 A inner join
(Select minid=min(id),maxid=max(id),name from table1 group by name) b on a.name=b.name
where a.id<>b.minid And a.id<>b.maxid
-----------------结果----------------------------------
select * from Table1
------------------------------------------------------
id name time
1 李 2006-10-10 09:00:00.000
5 李 2006-10-11 18:00:00.000
--try
create table table1
(
id int IDENTITY( 1,1) not null ,
name varchar(10),
时间 datetime
)
insert into table1
select '李','2006-10-10 09:00:00' union all
select '李','2006-10-10 10:00:00' union all
select '李','2006-10-10 18:00:00' union all
select '李','2006-10-11 09:00:00' union all
select '李','2006-10-11 18:00:00'select *from table1
--删除语句
delete
from table1
where 时间<(
select max(时间)
from table1
where name =table1.name
group by name,convert(varchar(10),时间,120)
having count(name)>=3
)
and 时间>(
select min(时间)
from table1
where name =table1.name
group by name,convert(varchar(10),时间,120)
having count(name)>=3
) select *from table1
--结果
id name 时间
----------- ---------- ------------------------------------------------------
1 李 2006-10-10 09:00:00.000
3 李 2006-10-10 18:00:00.000
4 李 2006-10-11 09:00:00.000
5 李 2006-10-11 18:00:00.000(所影响的行数为 4 行)--
insert into @t
select 1, '李' , '2006-10-10 09:00:00'
union all select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 10:00:00'
union all select 3, '李' , '2006-10-10 18:00:00'
union all select 4, '李' , '2006-10-11 09:00:00'
union all select 5, '李' , '2006-10-11 18:00:00'select name , convert(varchar(10),time,120) as time , min(time) as '打卡记录' from @t where convert(varchar(10),time,114) < '12:00:00' group by name , convert(varchar(10),time,120)
union all
select name , convert(varchar(10),time,120) as time , max(time) as '打卡记录' from @t where convert(varchar(10),time,114) > '12:00:00' group by name , convert(varchar(10),time,120)
order by name , timename time 打卡记录
---------- ---------- ------------------------------------------------------
李 2006-10-10 2006-10-10 09:00:00.000
李 2006-10-10 2006-10-10 18:00:00.000
李 2006-10-11 2006-10-11 09:00:00.000
李 2006-10-11 2006-10-11 18:00:00.000(所影响的行数为 4 行)
select name , min(time) from @t where convert(varchar(10),time,114) < '12:00:00' group by name
union all
select name , max(time) from @t where convert(varchar(10),time,114) > '12:00:00' group by name
order by name
insert into @t
select 1, '李' , '2006-10-10 09:00:00'
union all select 1, '李' , '2006-10-10 09:00:00'
union all select 2, '李' , '2006-10-10 10:00:00'
union all select 3, '李' , '2006-10-10 18:00:00'
union all select 4, '李' , '2006-10-11 09:00:00'
union all select 5, '李' , '2006-10-11 18:00:00'select name , convert(varchar(10),time,120) as time , min(time) as '打卡记录' from @t where convert(varchar(10),time,114) < '12:00:00' group by name , convert(varchar(10),time,120)
union all
select name , convert(varchar(10),time,120) as time , max(time) as '打卡记录' from @t where convert(varchar(10),time,114) > '12:00:00' group by name , convert(varchar(10),time,120)
order by name , timename time 打卡记录
---------- ---------- ------------------------------------------------------
李 2006-10-10 2006-10-10 09:00:00.000
李 2006-10-10 2006-10-10 18:00:00.000
李 2006-10-11 2006-10-11 09:00:00.000
李 2006-10-11 2006-10-11 18:00:00.000(所影响的行数为 4 行)