34,594
社区成员
发帖
与我相关
我的任务
分享
create table t_temp
(
TempId int identity(1,1) primary key,
TempName nvarchar(20),
AddTime datetime
)
insert into t_temp (TempName,AddTime) values('张三','2014-01-01 11:05:00')
insert into t_temp (TempName,AddTime) values('李四','2014-01-01 11:30:00')
insert into t_temp (TempName,AddTime) values('王五','2014-01-01 12:00:00')
insert into t_temp (TempName,AddTime) values('张三','2014-01-02 21:00:00')
insert into t_temp (TempName,AddTime) values('李四','2014-01-02 19:00:00')
insert into t_temp (TempName,AddTime) values('王五','2014-01-02 23:00:00')
go
select TempId,TempName,
case when rownum = 1 then convert(varchar(23),AddTime,120)
else substring(CONVERT(varchar(23),AddTime,120),12,8)
end as addtime
from
(
select *,
ROW_NUMBER() over(partition by convert(varchar(10),AddTime,120) order by getdate()) rownum
from t_temp
)t
/*
TempId TempName addtime
1 张三 2014-01-01 11:05:00
2 李四 11:30:00
3 王五 12:00:00
4 张三 2014-01-02 21:00:00
5 李四 19:00:00
6 王五 23:00:00
*/
;WITH a1 AS
(
select TempId,TempName,CONVERT(CHAR(19),AddTime,120) AddTime
From #t_temp
)
SELECT a.TempId,a.TempName,CASE WHEN b.AddTime IS NULL THEN a.AddTime ELSE RIGHT(a.AddTime,8) END AddTime
FROM a1 a
OUTER APPLY
(
SELECT TOP 1 AddTime AddTime
FROM a1 WHERE LEFT(AddTime,10)=LEFT(a.AddTime,10) AND AddTime<a.AddTime
ORDER BY AddTime
) b
order by a.AddTime
--create table t_temp
--(
-- TempId int identity(1,1) primary key,
-- TempName nvarchar(20),
-- AddTime datetime
--)
--insert into t_temp (TempName,AddTime) values('张三','2014-01-01 11:05:00')
--insert into t_temp (TempName,AddTime) values('李四','2014-01-01 11:30:00')
--insert into t_temp (TempName,AddTime) values('王五','2014-01-01 12:00:00')
--insert into t_temp (TempName,AddTime) values('张三','2014-01-02 21:00:00')
--insert into t_temp (TempName,AddTime) values('李四','2014-01-02 19:00:00')
--insert into t_temp (TempName,AddTime) values('王五','2014-01-02 23:00:00')
select a.tempid,a.tempname,CASE WHEN CONVERT(VARCHAR(10),b.addtime,120)=CONVERT(VARCHAR(10),a.addtime,120) THEN CONVERT(VARCHAR(30),CONVERT(VARCHAR(10),b.addtime,108)) ELSE CONVERT(VARCHAR(30),a.addtime,121) END addtime
From t_temp a LEFT JOIN t_temp b ON a.TempId=b.TempId+1
order by a.tempid,a.AddTime
/*
tempid tempname addtime
----------- -------------------- ------------------------------
1 张三 2014-01-01 11:05:00.000
2 李四 11:05:00
3 王五 11:30:00
4 张三 2014-01-02 21:00:00.000
5 李四 21:00:00
6 王五 19:00:00
*/