表TB
NAME VALUE TIME
A 100 2009-11-01
A 110 2009-11-02
A 120 2009-11-03
B 110 2009-11-01
B 120 2009-11-02
B 130 2009-11-03
C 130 2009-11-03-----------------
想得到结果:姓名 最新更新值 上次更新值
A 120 110
B 130 120
C 130 --------------
备注:显示出最新两次更新值,时间理论上不会重复
NAME VALUE TIME
A 100 2009-11-01
A 110 2009-11-02
A 120 2009-11-03
B 110 2009-11-01
B 120 2009-11-02
B 130 2009-11-03
C 130 2009-11-03-----------------
想得到结果:姓名 最新更新值 上次更新值
A 120 110
B 130 120
C 130 --------------
备注:显示出最新两次更新值,时间理论上不会重复
-----------------DECLARE @TA TABLE(namex varchar(10),valuex int,date datetime)INSERT INTO @TA
SELECT 'A',100,'2009-11-01' UNION ALL
SELECT 'A',110,'2009-11-02' UNION ALL
SELECT 'A',120,'2009-11-03' UNION ALL
SELECT 'B',110,'2009-11-01' UNION ALL
SELECT 'B',120,'2009-11-02' UNION ALL
SELECT 'B',130,'2009-11-03' UNION ALL
SELECT 'C',120,'2009-11-03' SELECT A.NAMEX,A.DATE,CASE WHEN (SELECT COUNT(1) FROM @TA WHERE namex=a.namex)>1 THEN a.valuex ELSE NULL END
FROM @TA A WHERE NOT EXISTS(SELECT 1 FROM @TA B WHERE a.date<b.date and a.namex=b.namex)
declare @tb table([NAME] varchar(1),[VALUE] int,[TIME] datetime)
insert @tb
select 'A',100,'2009-11-01' union all
select 'A',110,'2009-11-02' union all
select 'A',120,'2009-11-03' union all
select 'B',110,'2009-11-01' union all
select 'B',120,'2009-11-02' union all
select 'B',130,'2009-11-03' union all
select 'C',130,'2009-11-03'
select *
from (select * , id=row_number() over (partition by [NAME] order by [NAME],[TIME] desc) from @tb ) p
where id<3/*
NAME VALUE TIME id
---- ----------- ----------------------- --------------------
A 120 2009-11-03 00:00:00.000 1
A 110 2009-11-02 00:00:00.000 2
B 130 2009-11-03 00:00:00.000 1
B 120 2009-11-02 00:00:00.000 2
C 130 2009-11-03 00:00:00.000 1(5 row(s) affected)
A 2009-11-03 00:00:00.000 120
B 2009-11-03 00:00:00.000 130
C 2009-11-03 00:00:00.000 NULL错了,我要的是两个值,根本不要日期
go
--> -->
if not object_id(N'Tempdb..#TB') is null
drop table #TB
Go
Create table #TB([NAME] nvarchar(1),[VALUE] int,[TIME] Datetime)
Insert #TB
select N'A',100,'2009-11-01' union all
select N'A',110,'2009-11-02' union all
select N'A',120,'2009-11-03' union all
select N'B',110,'2009-11-01' union all
select N'B',120,'2009-11-02' union all
select N'B',130,'2009-11-03' union all
select N'C',130,'2009-11-03'
Go
Select
t.[NAME],t.[VALUE],isnull(rtrim(t2.[VALUE]),'') as [VALUE2]from #TB t
left join #TB t2 on t.[NAME]=t2.[NAME] and t2.[TIME]=(select max([TIME]) from #TB where [NAME]=t.[NAME] and [TIME]<t.[TIME])
where not exists(select 1 from #TB where [NAME]=t.[NAME] and [TIME]>t.[TIME])
(7 個資料列受到影響)
NAME VALUE VALUE2
---- ----------- ------------
A 120 110
B 130 120
C 130 (3 個資料列受到影響)
declare @tb table([NAME] varchar(1),[VALUE] int,[TIME] datetime)
insert @tb
select 'A',100,'2009-11-01' union all
select 'A',110,'2009-11-02' union all
select 'A',120,'2009-11-03' union all
select 'B',110,'2009-11-01' union all
select 'B',120,'2009-11-02' union all
select 'B',130,'2009-11-03' union all
select 'C',130,'2009-11-03';with cte as
(
select *
from (select * , id=row_number() over (partition by [NAME] order by [NAME],[TIME] desc) from @tb ) p
where id<3
)
SELECT DISTINCT T1.NAME, T1.VALUE, T2.VALUE
FROM CTE T1 LEFT JOIN CTE T2 ON T1.NAME = T2.NAME AND T1.TIME>T2.TIME
WHERE T1.ID=1/*
NAME VALUE VALUE
---- ----------- -----------
A 120 110
B 130 120
C 130 NULL(3 row(s) affected)
with cte
as
(
select *,ROW_NUMBER()over(partition by namex order by [date] desc) as pos from tb
)
select distinct namex,(select valuex from cte where pos=1 and a.namex=namex) as '最近更新',(select valuex from cte where pos=2 and a.namex=namex) as '上次更新' from cte a where pos<=2
insert into @tb select 'A',100,'2009-11-01'
union all select 'A',110,'2009-11-02'
union all select 'A',120,'2009-11-03'
union all select 'B',110,'2009-11-01'
union all select 'B',120,'2009-11-02'
union all select 'B',130,'2009-11-03'
union all select 'C',130,'2009-11-03'
--select * from @tb
select name,value,lastedit = isnull((select top 1 value as v from @tb where name = a.name and time < a.time order by time desc),'') from @tb a where not exists (select 1 from @tb where time > a.time)
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([NAME] varchar(1),[VALUE] int,[TIME] datetime)
insert [TB]
select 'A',100,'2009-11-01' union all
select 'A',110,'2009-11-02' union all
select 'A',120,'2009-11-03' union all
select 'B',110,'2009-11-01' union all
select 'B',120,'2009-11-02' union all
select 'B',130,'2009-11-03' union all
select 'C',130,'2009-11-03'
select 姓名=NAME,
最新更新值=max(case when flag=1 then VALUE else 0 end),
上次更新值=max(case when flag=2 then VALUE else 0 end)
from (
select *,flag=(select count(1)+1 from TB where t.Name=Name and TIME>t.TIME) from [TB] t)g
group by NAME/*
姓名 最新更新值 上次更新值
---- ----------- -----------
A 120 110
B 130 120
C 130 0(所影响的行数为 3 行)*/drop table TB
if object_id[tb]is not null
drop table [tb]
create table [tb] ([name] varchar(20),[value] int,[time] datetime)
insert [tb]
select 'A', '100','2009-11-01' union all
select 'A', '110','2009-11-02' union all
select 'A', '110','2009-11-02' union all
select 'A', '120','2009-11-03' union all
select 'B', '110','2009-11-01' union all
select 'B', '110','2009-11-01' union all
select 'B', '120','2009-11-02' union all
select 'B', '130','2009-11-03' union all
select 'c', '130','2009-11-03'
--结果
SELECT A.NAMEX,A.DATE,CASE WHEN (SELECT COUNT(1) FROM @TA WHERE namex=a.namex)>1 THEN a.valuex ELSE NULL END
FROM @TA A WHERE NOT EXISTS(SELECT 1 FROM @TA B WHERE a.date<b.date and a.namex=b.namex)
declare @tb table (Name nvarchar(10),value int,time datetime)
insert into @tb select 'A',100,'2009-11-01'
union all select 'A',110,'2009-11-02'
union all select 'A',120,'2009-11-03'
union all select 'B',110,'2009-11-01'
union all select 'B',120,'2009-11-02'
union all select 'B',130,'2009-11-03'
union all select 'C',130,'2009-11-03'
select b.name as 姓名,最近更新值=b.value,上次更新值=a.value from @tb a right join
(select * from @tb a where not exists
(select 1 from @tb where a.name=name and value>a.value)) b
on a.name=b.name and a.time=b.time-1
/*
姓名 最近更新值 上次更新值
---------- ----------- -----------
A 120 110
B 130 120
C 130 NULL(3 行受影响)
*/