CREATE TABLE [T1] ([id] [int] IDENTITY (1, 1) NOT NULL ,[Name] nvarchar(30),[intoTime] Datetime)
Insert into[T1] select 'Tom Li','2011-1-10 9:00'
Insert into[T1] select 'Tom Li','2011-1-10 15:00'
Insert into[T1] select 'Tom Li','2011-1-10 18:50'
Insert into[T1] select 'Richard Wu','2011-1-10 8:48'
Insert into[T1] select 'Richard Wu','2011-1-10 10:44'
Insert into[T1] select 'Richard Wu','2011-1-10 19:10'
Insert into[T1] select 'Richard Wu','2011-1-11 9:00'
/*需要的结果(每个人每天的打卡记录)
姓名(Name) 打卡时间(intoTime)
Tom Li 2011-1-10 9:00
Tom Li 2011-1-10 18:50
Richard Wu 2011-1-10 8:48
Richard Wu 2011-1-10 19:10
Richard Wu 2011-1-11 9:00
*/
drop table T1
Insert into[T1] select 'Tom Li','2011-1-10 9:00'
Insert into[T1] select 'Tom Li','2011-1-10 15:00'
Insert into[T1] select 'Tom Li','2011-1-10 18:50'
Insert into[T1] select 'Richard Wu','2011-1-10 8:48'
Insert into[T1] select 'Richard Wu','2011-1-10 10:44'
Insert into[T1] select 'Richard Wu','2011-1-10 19:10'
Insert into[T1] select 'Richard Wu','2011-1-11 9:00'
/*需要的结果(每个人每天的打卡记录)
姓名(Name) 打卡时间(intoTime)
Tom Li 2011-1-10 9:00
Tom Li 2011-1-10 18:50
Richard Wu 2011-1-10 8:48
Richard Wu 2011-1-10 19:10
Richard Wu 2011-1-11 9:00
*/
drop table T1
Insert into[T1] select 'Tom Li','2011-1-10 9:00'
Insert into[T1] select 'Tom Li','2011-1-10 15:00'
Insert into[T1] select 'Tom Li','2011-1-10 18:50'
Insert into[T1] select 'Richard Wu','2011-1-10 8:48'
Insert into[T1] select 'Richard Wu','2011-1-10 10:44'
Insert into[T1] select 'Richard Wu','2011-1-10 19:10'
Insert into[T1] select 'Richard Wu','2011-1-11 9:00' select distinct name , intoTime = (select min(intoTime) from t1 where name = t.name and datediff(dd,intoTime,t.intoTime) = 0) from t1 t
union all
select distinct name , intoTime = (select max(intoTime) from t1 where name = t.name and datediff(dd,intoTime,t.intoTime) = 0) from t1 t
order by name , intoTimedrop table t1/*
name intoTime
------------------------------ ------------------------------------------------------
Richard Wu 2011-01-10 08:48:00.000
Richard Wu 2011-01-10 19:10:00.000
Richard Wu 2011-01-11 09:00:00.000
Richard Wu 2011-01-11 09:00:00.000
Tom Li 2011-01-10 09:00:00.000
Tom Li 2011-01-10 18:50:00.000(所影响的行数为 6 行)
*/
Insert into[T1] select 'Tom Li','2011-1-10 9:00'
Insert into[T1] select 'Tom Li','2011-1-10 15:00'
Insert into[T1] select 'Tom Li','2011-1-10 18:50'
Insert into[T1] select 'Richard Wu','2011-1-10 8:48'
Insert into[T1] select 'Richard Wu','2011-1-10 10:44'
Insert into[T1] select 'Richard Wu','2011-1-10 19:10'
Insert into[T1] select 'Richard Wu','2011-1-11 9:00' select distinct name , intoTime from
(
select distinct name , intoTime = (select min(intoTime) from t1 where name = t.name and datediff(dd,intoTime,t.intoTime) = 0) from t1 t
union all
select distinct name , intoTime = (select max(intoTime) from t1 where name = t.name and datediff(dd,intoTime,t.intoTime) = 0) from t1 t
) t
order by name , intoTimedrop table t1/*
name intoTime
------------------------------ ------------------------------------------------------
Richard Wu 2011-01-10 08:48:00.000
Richard Wu 2011-01-10 19:10:00.000
Richard Wu 2011-01-11 09:00:00.000
Tom Li 2011-01-10 09:00:00.000
Tom Li 2011-01-10 18:50:00.000(所影响的行数为 5 行)
*/
CREATE TABLE [T1] ([id] [int] IDENTITY (1, 1) NOT NULL ,[Name] nvarchar(30),[intoTime] Datetime)
Insert into[T1] select 'Tom Li','2011-1-10 9:00'
Insert into[T1] select 'Tom Li','2011-1-10 15:00'
Insert into[T1] select 'Tom Li','2011-1-10 18:50'
Insert into[T1] select 'Richard Wu','2011-1-10 8:48'
Insert into[T1] select 'Richard Wu','2011-1-10 10:44'
Insert into[T1] select 'Richard Wu','2011-1-10 19:10'
Insert into[T1] select 'Richard Wu','2011-1-11 9:00'select name,intoTime
from t1
order by convert(varchar(10),intoTime,112),namedrop table t1name intoTime
------------------------------ -----------------------
Richard Wu 2011-01-10 08:48:00.000
Richard Wu 2011-01-10 10:44:00.000
Richard Wu 2011-01-10 19:10:00.000
Tom Li 2011-01-10 09:00:00.000
Tom Li 2011-01-10 15:00:00.000
Tom Li 2011-01-10 18:50:00.000
Richard Wu 2011-01-11 09:00:00.000(7 行受影响)
(select Name,min(intotime) from T1 group by name
unoin all
select Name,max(intotime) from T1 group by name)
order by name,intotime
from t1 a
where not exists (
select 1 from T1
where name = a.name
and convert(varchar(8),intoTime,112) = convert(varchar(8),a.intoTime,112)
and intoTime,112) < a.intoTime
)
or not exists (
select 1 from T1
where name = a.name
and convert(varchar(8),intoTime,112) = convert(varchar(8),a.intoTime,112)
and intoTime,112) > a.intoTime
)
(select Name,min(intotime) from T1 group by name,convert(varchar(20),intotime(),112)
unoin all
select Name,max(intotime) from T1 group by name),convert(varchar(20),intotime(),112)
order by name,intotime
(select Name,min(intotime) from T1 group by name,convert(varchar(20),intotime,112)
unoin all
select Name,max(intotime) from T1 group by name,convert(varchar(20),intotime,112))
order by name,intotime
(涩(*^__^*)郎) 是 union 不是 unoin
在关键字 'order' 附近有语法错误。
CREATE TABLE [T1] ([id] [int] IDENTITY (1, 1) NOT NULL ,[Name] nvarchar(30),[intoTime] Datetime)
Insert into[T1] select 'Tom Li','2011-1-10 9:00'
Insert into[T1] select 'Tom Li','2011-1-10 15:00'
Insert into[T1] select 'Tom Li','2011-1-10 18:50'
Insert into[T1] select 'Richard Wu','2011-1-10 8:48'
Insert into[T1] select 'Richard Wu','2011-1-10 10:44'
Insert into[T1] select 'Richard Wu','2011-1-10 19:10'
Insert into[T1] select 'Richard Wu','2011-1-11 9:00' select t.name,intotime from t1 t where
not exists(select 1 from t1 where name = t.name and datediff(dd,intoTime,t.intoTime) = 0 and intoTime < t.intoTime)
or
not exists(select 1 from t1 where name = t.name and datediff(dd,intoTime,t.intoTime) = 0 and intoTime > t.intoTime)drop table t1/*
name intotime
------------------------------ ------------------------------------------------------
Tom Li 2011-01-10 09:00:00.000
Tom Li 2011-01-10 18:50:00.000
Richard Wu 2011-01-10 08:48:00.000
Richard Wu 2011-01-10 19:10:00.000
Richard Wu 2011-01-11 09:00:00.000(所影响的行数为 5 行)
*/
(select Name,min(intotime)as intotime from T1 group by name,convert(varchar(20),intotime,112)
union all
select Name,max(intotime) as intotime from T1 group by name,convert(varchar(20),intotime,112)) a
order by a.name,a.intotime