delete a from 表 a
where exists(
select 1 from 表 where id=a.id and
time <>(select min(time) from 表 where id=a.id )
and
time <>(select max(time) from 表 where id=a.id )
)
where exists(
select 1 from 表 where id=a.id and
time <>(select min(time) from 表 where id=a.id )
and
time <>(select max(time) from 表 where id=a.id )
)
card_id ,
convert(varchar(10),sign_time,120) as 日期
min(sign_time) as 当天最早,
max(sign_time) as 当天最晚
from
timerecords
group by card_id,convert(varchar(10),sign_time,120)
select * from timerecords a
where not exists(select 1 from timerecords b where a.id=b.id and a.time>b.time )
union all
select * from timerecords a
where not exists(select 1 from timerecords b where a.id=b.id and a.time<b.time )
where not exists(select 1 from timerecords where card_id=a.card_id and datediff
(dd,sign_time,a.sign_time)=0 and sign_time>a.sign_time)
union all
select * from timerecords a
where not exists(select 1 from timerecords where card_id=a.card_id and datediff
(dd,sign_time,a.sign_time)=0 and sign_time<a.sign_time)
*
from
timerecords a
where
sign_time=(select max(sign_time) from timerecords where card_id=a.card_id and datediff(d,sign_time,a.sign_time )=0)
or
sign_time=(select min(sign_time) from timerecords where card_id=a.card_id and datediff(d,sign_time,a.sign_time )=0)
union all
select card_id , max(sign_time) sign_time_max from timerecords group by card_id
select card_id , min(sign_time) sign_time_min from timerecords group by card_id
union all
select card_id , max(sign_time) sign_time_max from timerecords group by card_id--如果不止card_id , sign_time两个字段.
select t.* from timerecords t where sign_time = (select min(sign_time) from timerecords where card_id = t.card_id)
union all
select t.* from timerecords t where sign_time = (select max(sign_time) from timerecords where card_id = t.card_id)