有一表Test 字段有sid, name, number, updateDate等
列出对某一个字段的修改历史,如果相邻记录有该字段值有相同话取最早的
sid name number updateDate
1 a 111 2009-11-10 0:00:00
2 a 222 2009-11-10 10:00:00
3 b 222 2009-11-11 0:00:00
4 c 222 2009-11-12 0:00:00
5 c 111 2009-11-13 0:00:00
6 c 222 2009-11-14 0:00:00
7 c 333 2009-11-15 0:00:00
8 d 333 2009-11-16 0:00:00
9 b 333 2009-11-17 0:00:00
10 e 444 2009-11-18 0:00:00
11 f 444 2009-11-19 0:00:00
求写一个sql函数,给出某个字段名(如number),列出对该字段修改的记录列表
所要的结果为如下:
1 a 111 2009-11-10 0:00:00
2 a 222 2009-11-10 10:00:00(number为222的紧接着的还有Sid=3和Sid=4两条,取最早修改的记录)
5 c 111 2009-11-13 0:00:00
6 c 222 2009-11-14 0:00:00
7 c 333 2009-11-15 0:00:00(number为333的紧接着的还有Sid=8和Sid=9的两条,取最早修改的记录)
10 e 444 2009-11-18 0:00:00(number为444的紧接着的还有Sid=10的一条,取最早修改的记录)
列出对某一个字段的修改历史,如果相邻记录有该字段值有相同话取最早的
sid name number updateDate
1 a 111 2009-11-10 0:00:00
2 a 222 2009-11-10 10:00:00
3 b 222 2009-11-11 0:00:00
4 c 222 2009-11-12 0:00:00
5 c 111 2009-11-13 0:00:00
6 c 222 2009-11-14 0:00:00
7 c 333 2009-11-15 0:00:00
8 d 333 2009-11-16 0:00:00
9 b 333 2009-11-17 0:00:00
10 e 444 2009-11-18 0:00:00
11 f 444 2009-11-19 0:00:00
求写一个sql函数,给出某个字段名(如number),列出对该字段修改的记录列表
所要的结果为如下:
1 a 111 2009-11-10 0:00:00
2 a 222 2009-11-10 10:00:00(number为222的紧接着的还有Sid=3和Sid=4两条,取最早修改的记录)
5 c 111 2009-11-13 0:00:00
6 c 222 2009-11-14 0:00:00
7 c 333 2009-11-15 0:00:00(number为333的紧接着的还有Sid=8和Sid=9的两条,取最早修改的记录)
10 e 444 2009-11-18 0:00:00(number为444的紧接着的还有Sid=10的一条,取最早修改的记录)
from
tb t1
left join tb t2 on t1.sid=t2.sid+1
where t2.sid is null or t1.name<>t2.name
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-17 23:27:53
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([sid] int,[name] varchar(1),[number] int,[updateDate] datetime)
insert [tb]
select 1,'a',111,'2009-11-10 0:00:00' union all
select 2,'a',222,'2009-11-10 10:00:00' union all
select 3,'b',222,'2009-11-11 0:00:00' union all
select 4,'c',222,'2009-11-12 0:00:00' union all
select 5,'c',111,'2009-11-13 0:00:00' union all
select 6,'c',222,'2009-11-14 0:00:00' union all
select 7,'c',333,'2009-11-15 0:00:00' union all
select 8,'d',333,'2009-11-16 0:00:00' union all
select 9,'b',333,'2009-11-17 0:00:00' union all
select 10,'e',444,'2009-11-18 0:00:00' union all
select 11,'f',444,'2009-11-19 0:00:00'
--------------开始查询--------------------------
;with f as
(
--select id=identity(int,1,1),* into tb1 from tb
select
[sid],[name],[number],[updateDate],id0=(select count(1) from (select id=row_number()over(order by getdate()),* from tb)t where [number]=a.[number] and id<=a.id
and
id>=(select isnull(max(id),0) from ((select id=row_number()over(order by getdate()),* from tb))t where id<a.id and [number]!=a.[number]))
from
(select id=row_number()over(order by getdate()),* from tb) a
)
select sid,name,number,updateDate from f t where id0=(select max(id0) from f where number=t.number)----------------结果----------------------------
/* sid name number updateDate
----------- ---- ----------- -----------------------
1 a 111 2009-11-10 00:00:00.000
4 c 222 2009-11-12 00:00:00.000
5 c 111 2009-11-13 00:00:00.000
9 b 333 2009-11-17 00:00:00.000
11 f 444 2009-11-19 00:00:00.000(5 行受影响)
*/
--给点思路,先这样
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-17 23:27:53
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([sid] int,[name] varchar(1),[number] int,[updateDate] datetime)
insert [tb]
select 1,'a',111,'2009-11-10 0:00:00' union all
select 2,'a',222,'2009-11-10 10:00:00' union all
select 3,'b',222,'2009-11-11 0:00:00' union all
select 4,'c',222,'2009-11-12 0:00:00' union all
select 5,'c',111,'2009-11-13 0:00:00' union all
select 6,'c',222,'2009-11-14 0:00:00' union all
select 7,'c',333,'2009-11-15 0:00:00' union all
select 8,'d',333,'2009-11-16 0:00:00' union all
select 9,'b',333,'2009-11-17 0:00:00' union all
select 10,'e',444,'2009-11-18 0:00:00' union all
select 11,'f',444,'2009-11-19 0:00:00'
--------------开始查询--------------------------
--;with f as
--(
--select id=identity(int,1,1),* into tb1 from tb
select
[sid],[name],[number],[updateDate],id0=(select count(1) from (select id=row_number()over(order by getdate()),* from tb)t where [number]=a.[number] and id<=a.id
and
id>=(select isnull(max(id),0) from ((select id=row_number()over(order by getdate()),* from tb))t where id<a.id and [number]!=a.[number]))
from
(select id=row_number()over(order by getdate()),* from tb) a
--)
--select sid,name,number,updateDate from f t where updateDate=(select max(updateDate) from f where number=t.number)----------------结果----------------------------
/*sid name number updateDate id0
----------- ---- ----------- ----------------------- -----------
1 a 111 2009-11-10 00:00:00.000 1
2 a 222 2009-11-10 10:00:00.000 1
3 b 222 2009-11-11 00:00:00.000 2
4 c 222 2009-11-12 00:00:00.000 3
5 c 111 2009-11-13 00:00:00.000 1
6 c 222 2009-11-14 00:00:00.000 1
7 c 333 2009-11-15 00:00:00.000 1
8 d 333 2009-11-16 00:00:00.000 2
9 b 333 2009-11-17 00:00:00.000 3
10 e 444 2009-11-18 00:00:00.000 1
11 f 444 2009-11-19 00:00:00.000 2(11 行受影响)*/
returns datetime
as
begin
declare @time datetime
declare @time1 datetime
declare @i int
set @i=1select @time=updateDate from tb where sid=@sid-1 and number=@number
select @time1=updateDate from tb where sid=@sid and number=@number
if @time is null
return @time1
else
begin
while @i<@sid
begin
select @time=updateDate from tb where sid=@sid-@i and number=@number
set @i=@i+1
end
end
return @time
endselect * from tb where updateDate in
(select distinct dbo.getTime(sid,number) updateDate from tb)
where a.numbers=b.numbers and a.ID=b.ID+1 )