BeginTime BtsId Total68PhyRCENum
2009-9-30 0:00 1 192
2009-10-1 0:00 1 192
2009-10-2 0:00 1 192
2009-10-3 0:00 1 192
2009-10-4 0:00 1 256
2009-10-5 0:00 1 256
2009-10-6 0:00 1 256
2009-10-7 0:00 1 256
2009-10-8 0:00 1 256
2009-10-9 0:00 1 256如何根据Total68PhyRCENum发生变换后,替换出该条记录
2009-9-30 0:00 1 192
2009-10-1 0:00 1 192
2009-10-2 0:00 1 192
2009-10-3 0:00 1 192
2009-10-4 0:00 1 256
2009-10-5 0:00 1 256
2009-10-6 0:00 1 256
2009-10-7 0:00 1 256
2009-10-8 0:00 1 256
2009-10-9 0:00 1 256如何根据Total68PhyRCENum发生变换后,替换出该条记录
2009-9-30 0:00 1 192
2009-10-1 0:00 1 192
2009-10-2 0:00 1 192
2009-10-3 0:00 1 192
2009-10-4 0:00 1 256
2009-10-5 0:00 1 256
2009-10-6 0:00 1 256
2009-10-7 0:00 1 256
2009-10-8 0:00 1 256
2009-10-9 0:00 1 256 如何根据Total68PhyRCENum发生变换后,提取出红色这条记录
-- I love curssor......
select *,id=identity(int,1,1) into #t from tb1select b.* from #t a,#t bwhere a.id=b.id-1 and a.Total68PhyRCENum!=b.Total68PhyRCENum
group by Total68PhyRCENum
where exists
(select * from tb where t.BeginTime=dateadd(day,1,BeginTime) and BtsId =t.BtsId
and t.Total68PhyRCENum=Total68PhyRCENum )
create FUNCTION [dbo].[fn_getPriorRow1010]
(
@intbstID INTEGER,
@dtBeginTime DATETIME
)
RETURNS DATETIME
AS
BEGIN
DECLARE @dtPriorbegintime DATETIME
DECLARE @dtMinbegintime DATETIME
DECLARE @intFoundRow INTEGER
DECLARE @intMonths INTEGER
DECLARE @intMaxMonths INTEGER SET @intMonths = 1
SET @dtMinbegintime = (SELECT MIN(begintime) FROM tablename11
WHERE bstID = @intbstID) IF @dtMinbegintime = @dtbegintime RETURN @dtbegintime SET @intMaxMonths = DATEDIFF(DAY, @dtMinbegintime, @dtbegintime)
WHILE @intMonths <= @intMaxMonths
BEGIN
SET @dtPriorbegintime = DATEADD(day, -@intMonths, @dtbegintime)
SET @intFoundRow = (SELECT COUNT(*) FROM tablename11
WHERE begintime = @dtPriorbegintime
AND bstID = @intbstID)
IF @intFoundRow >= 1 BREAK
IF @intFoundRow = 0
BEGIN
SET @intMonths = @intMonths + 1
CONTINUE
END
END
RETURN @dtPriorbegintime
ENDcreate table 要取的结果表(begintime datetime null,
BstID bigint null,
Total68PhyRCENum int null)DECLARE @vchExecStmt VARCHAR(4000)
DECLARE @vchbegintime VARCHAR(20)
DECLARE @dtMinbegintime DATETIME
DECLARE @dtMaxbegintime DATETIME
DECLARE @dtbegintime DATETIME
DECLARE @vchMinbegintime VARCHAR(20)
DECLARE @vchMaxbegintime VARCHAR(20)
DECLARE @insert VARCHAR(1000)
SET @dtMinbegintime = CAST('9/30/2009' AS DATETIME)
SET @dtMaxbegintime = CAST('10/9/2009' AS DATETIME)
SET @dtbegintime = @dtMinbegintime
SET @vchbegintime = CAST(@dtbegintime AS VARCHAR(20))
WHILE @dtbegintime <= @dtMaxbegintime
BEGIN
SET @vchExecStmt = '
insert into 要取的结果表
select aa.*
FROM dbo.tablename11 aa,
dbo.tablename11 pre
WHERE aa.begintime = CAST(''' + @vchbegintime + ''' AS DATETIME)
AND pre.bstid = aa.bstid and aa.Total68PhyRCENum<>pre.Total68PhyRCENum
AND pre.begintime =dbo.fn_getPriorRow1010(aa.bstid,aa.begintime) ' PRINT @vchExecStmt
PRINT 'GO' SET @dtbegintime = DATEADD(day, 1, @dtbegintime)
SET @vchbegintime = CAST(@dtbegintime AS VARCHAR(20))END这样不知道,能不能达到你要的效果