select 字段1, case when a.字段1=a then a.字段2 else b.字段2 end as 字段2 from tb as a,tb as b where a.时间字段=b.时间字段+1
select tb1.字段1 ,IsNull(tb2.字段2,tb1.字段2) from tb1 left join tb tb2 on tb1.时间字段-1=tb2.tb2
select 字段1,字段2=isnull((select top 1 字段1 from ta where 时间字段 > a.时间字段 order by 时间字段 desc),字段2) from ta a
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-21 14:22:44 -- 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.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([字段1] varchar(1),[字段2] varchar(2),[时间字段] varchar(4)) insert [tb] select 'a','01','0910' union all select 'b','01','0909' union all select 'c','01','0908' union all select 'd','01','0907' --------------开始查询--------------------------select *,id=identity (int) into #t from [tb] select [字段1],[字段2] from tb where 字段1='a' union all select b.[字段1],[字段2]=b.[字段1] from #t a,#t b where a.id=b.id-1 drop table #t ----------------结果---------------------------- /* 字段1 字段2 ---- ---- a 01 b b c c d d(4 行受影响) */
select 字段1,字段2=isnull((select top 1 字段1 from ta where 时间字段 > a.时间字段 order by 时间字段 ),字段2) from ta a
--> 测试数据:@tb declare @tb table([字段1] varchar(10),[字段2] varchar(20),[时间字段] varchar(40)) insert @tb select 'a','01','0910' union all select 'b','01','0909' union all select 'c','01','0908' union all select 'd','01','0907' select 字段1,字段2=isnull((select 字段1 from @tb where 时间字段=t.时间字段+1),字段2) from @tb t/*字段1 字段2 ---------- ---------- a 01 b a c b d c(4 行受影响)*/
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-21 14:22:44 -- 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.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([字段1] varchar(1),[字段2] varchar(2),[时间字段] varchar(4)) insert [tb] select 'a','01','0910' union all select 'b','01','0909' union all select 'c','01','0908' union all select 'd','01','0907' --------------开始查询--------------------------select *,id=identity (int) into #t from [tb] select [字段1],[字段2] from tb where 字段1='a' union all select b.[字段1],[字段2]=a.[字段1] from #t a,#t b where a.id=b.id-1 drop table #t ----------------结果---------------------------- /* 字段1 字段2 ---- ---- a 01 b a c b d c(4 行受影响)*/
---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-10-21 14:22:44 -- 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.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([字段1] varchar(1),[字段2] varchar(2),[时间字段] varchar(4)) insert [tb] select 'a','01','0910' union all select 'b','01','0909' union all select 'c','01','0908' union all select 'd','01','0907' --------------开始查询--------------------------select *,id=identity (int) into #t from [tb] select [字段1],[字段2] from tb where 时间字段=(select max(时间字段)from tb ) union all select b.[字段1],[字段2]=a.[字段1] from #t a,#t b where a.id=b.id-1 drop table #t ----------------结果---------------------------- /* 字段1 字段2 ---- ---- a 01 b a c b d c(4 行受影响)*/
declare @tb table([字段1] varchar(10),[字段2] varchar(20),[时间字段] varchar(40)) insert @tb select 'a','01','0910' union all select 'b','01','0909' union all select 'c','01','0908' union all select 'd','01','0907'select 字段1,字段2=isnull((select 字段1 from @tb where 时间字段=t.时间字段+1),字段2) from @tb t
case when a.字段1=a then a.字段2 else b.字段2 end as 字段2
from tb as a,tb as b
where a.时间字段=b.时间字段+1
select tb1.字段1 ,IsNull(tb2.字段2,tb1.字段2) from tb1
left join tb tb2 on tb1.时间字段-1=tb2.tb2
from ta a
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-21 14:22:44
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] varchar(1),[字段2] varchar(2),[时间字段] varchar(4))
insert [tb]
select 'a','01','0910' union all
select 'b','01','0909' union all
select 'c','01','0908' union all
select 'd','01','0907'
--------------开始查询--------------------------select *,id=identity (int) into #t from [tb]
select [字段1],[字段2] from tb where 字段1='a'
union all
select b.[字段1],[字段2]=b.[字段1] from #t a,#t b where a.id=b.id-1
drop table #t
----------------结果----------------------------
/* 字段1 字段2
---- ----
a 01
b b
c c
d d(4 行受影响)
*/
from ta a
declare @tb table([字段1] varchar(10),[字段2] varchar(20),[时间字段] varchar(40))
insert @tb
select 'a','01','0910' union all
select 'b','01','0909' union all
select 'c','01','0908' union all
select 'd','01','0907'
select 字段1,字段2=isnull((select 字段1 from @tb where 时间字段=t.时间字段+1),字段2) from @tb t/*字段1 字段2
---------- ----------
a 01
b a
c b
d c(4 行受影响)*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-21 14:22:44
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] varchar(1),[字段2] varchar(2),[时间字段] varchar(4))
insert [tb]
select 'a','01','0910' union all
select 'b','01','0909' union all
select 'c','01','0908' union all
select 'd','01','0907'
--------------开始查询--------------------------select *,id=identity (int) into #t from [tb]
select [字段1],[字段2] from tb where 字段1='a'
union all
select b.[字段1],[字段2]=a.[字段1] from #t a,#t b where a.id=b.id-1
drop table #t
----------------结果----------------------------
/* 字段1 字段2
---- ----
a 01
b a
c b
d c(4 行受影响)*/
9月7日时, 01这个货号经过再加工号生成了d,
9月8日时, 发现d还是不合格,这样再加工生成了c
以此类推
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-10-21 14:22:44
-- 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.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([字段1] varchar(1),[字段2] varchar(2),[时间字段] varchar(4))
insert [tb]
select 'a','01','0910' union all
select 'b','01','0909' union all
select 'c','01','0908' union all
select 'd','01','0907'
--------------开始查询--------------------------select *,id=identity (int) into #t from [tb]
select [字段1],[字段2] from tb where 时间字段=(select max(时间字段)from tb )
union all
select b.[字段1],[字段2]=a.[字段1] from #t a,#t b where a.id=b.id-1
drop table #t
----------------结果----------------------------
/* 字段1 字段2
---- ----
a 01
b a
c b
d c(4 行受影响)*/
insert @tb
select 'a','01','0910' union all
select 'b','01','0909' union all
select 'c','01','0908' union all
select 'd','01','0907'select 字段1,字段2=isnull((select 字段1 from @tb where 时间字段=t.时间字段+1),字段2) from @tb t