USE tempdb IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST; --> 如果表TEST不为空,删除表 GO CREATE TABLE TEST --> 创建表,字段及类型如下 ( col1 nvarchar(50), col2 nvarchar(50), col3 nvarchar(50) ); GO insert into TEST select '001', '0000000000', '2011-12-01' union all select '001', '0000000000', '2012-01-01' union all select '001', '0000000000', '2012-08-01' union all select '002', '0000000000', '2011-11-01' union all select '002', '0000000000', '2011-12-01' union all select '002', '0000000000', '2012-01-01' union all select '003', '0000000000', '2012-05-01' union all select '003', '0000000000', '2012-06-01' union all select '003', '0000000000', '2012-08-01'; with tb as (select ROW_NUMBER() over(PARTITION by col1 order by col1,col3) as rownum , col1,col2,col3 from test) select col1, isnull((select top 1 col3 from tb a where a.col1=b.col1 and a.rownum=b.rownum-1 ),convert(nvarchar(12),dateadd(month,-1,cast(col3 as datetime)),23)) as col2 ,col3 from tb b
IF OBJECT_ID('tempdb.dbo.#a') IS NOT NULL DROP TABLE #aCREATE TABLE #a([档案号] varchar(16),[上次抄表时间] varchar(16),[本次抄表时间] varchar(16)) INSERT INTO #a ( 档案号, 上次抄表时间, 本次抄表时间 ) SELECT '001','0000000000','2011-12-01' UNION ALL SELECT '001','0000000000','2012-01-01' UNION ALL SELECT '001','0000000000','2012-08-01' UNION ALL SELECT '002','0000000000','2011-11-01' UNION ALL SELECT '002','0000000000','2011-12-01' UNION ALL SELECT '002','0000000000','2012-01-01' UNION ALL SELECT '003','0000000000','2012-05-01' UNION ALL SELECT '003','0000000000','2012-06-01' UNION ALL SELECT '003','0000000000','2012-08-01'IF OBJECT_ID('tempdb.dbo.#B') IS NOT NULL DROP TABLE #BSELECT B1.[档案号],ISNULL(B2.[本次抄表时间],CONVERT(varchar(10),DATEADD(MONTH,-1,B1.[本次抄表时间]),120)) [上次抄表时间],B1.[本次抄表时间] INTO #B FROM (SELECT ROW_NUMBER() OVER(PARTITION BY [档案号] ORDER BY [档案号],[本次抄表时间]) Row,* FROM #A) B1 LEFT JOIN (SELECT ROW_NUMBER() OVER(PARTITION BY [档案号] ORDER BY [档案号],[本次抄表时间]) Row,* FROM #A) B2 ON B1.Row = B2.Row +1 AND B1.[档案号] = B2.[档案号]UPDATE #a SET [上次抄表时间] = B.[上次抄表时间] FROM #a A INNER JOIN #B B ON A.档案号 = B.[档案号] AND A.[本次抄表时间] = B.[本次抄表时间] SELECT * FROM #a DROP TABLE #a DROP TABLE #B
我喜欢“ttpsan550”的思路。简练,清晰。 学习了!!!
USE tempdb IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST; --> 如果表TEST不为空,删除表 GO CREATE TABLE TEST --> 创建表,字段及类型如下 ( col1 nvarchar(50), col2 nvarchar(50), col3 nvarchar(50) ); GO insert into TEST select '001', '0000000000', '2011-12-01' union all select '001', '0000000000', '2012-01-01' union all select '001', '0000000000', '2012-08-01' union all select '002', '0000000000', '2011-11-01' union all select '002', '0000000000', '2011-12-01' union all select '002', '0000000000', '2012-01-01' union all select '003', '0000000000', '2012-05-01' union all select '003', '0000000000', '2012-06-01' union all select '003', '0000000000', '2012-08-01'; with tb as (select ROW_NUMBER() over(PARTITION by col1 order by col1,col3) as rownum , col1,col2,col3 from test) update test1 set col2=isnull((select top 1 b.col3 from tb a where a.col1=b.col1 and a.rownum=b.rownum-1 ), convert(nvarchar(12),dateadd(month,-1,cast(b.col3 as datetime)),23)) from tb b ,TEST test1 where b.col1=TEST1.col1 and b.col3=test1.col3 --如果col1、col3能确定唯一行的话
select * from TEST
不好意思,一个地方给改错了 USE tempdb IF OBJECT_ID('TEST') IS NOT NULL DROP TABLE TEST; --> 如果表TEST不为空,删除表 GO CREATE TABLE TEST --> 创建表,字段及类型如下 ( col1 nvarchar(50), col2 nvarchar(50), col3 nvarchar(50) ); GO insert into TEST select '001', '0000000000', '2011-12-01' union all select '001', '0000000000', '2012-01-01' union all select '001', '0000000000', '2012-08-01' union all select '002', '0000000000', '2011-11-01' union all select '002', '0000000000', '2011-12-01' union all select '002', '0000000000', '2012-01-01' union all select '003', '0000000000', '2012-05-01' union all select '003', '0000000000', '2012-06-01' union all select '003', '0000000000', '2012-08-01'; with tb as (select ROW_NUMBER() over(PARTITION by col1 order by col1,col3) as rownum , col1,col2,col3 from test) update test1 set col2=isnull((select top 1 a.col3 from tb a where a.col1=b.col1 and a.rownum=b.rownum-1 ), convert(nvarchar(12),dateadd(month,-1,cast(b.col3 as datetime)),23)) from tb b ,TEST test1 where b.col1=TEST1.col1 and b.col3=test1.col3 --如果col1、col3能确定唯一行的话
上次抄表时间 = dateadd(month,-1,本次抄表时间)
海爷,楼主给的数据有些不符合这个update语句的效果啊
001 2011-11-01 2011-12-01
001 2011-12-01 2012-01-01
001 2012-01-01 2012-08-01
002 2012-10-01 2011-11-01
002 2012-11-01 2011-12-01
002 2012-12-01 2012-01-01
003 2012-04-01 2012-05-01
003 2012-05-01 2012-06-01
003 2012-06-01 2012-08-01
USE tempdb
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST; --> 如果表TEST不为空,删除表
GO
CREATE TABLE TEST --> 创建表,字段及类型如下
(
col1 nvarchar(50),
col2 nvarchar(50),
col3 nvarchar(50)
);
GO
insert into TEST
select
'001', '0000000000', '2011-12-01'
union all
select
'001', '0000000000', '2012-01-01'
union all
select
'001', '0000000000', '2012-08-01'
union all
select
'002', '0000000000', '2011-11-01'
union all
select
'002', '0000000000', '2011-12-01'
union all
select
'002', '0000000000', '2012-01-01'
union all
select
'003', '0000000000', '2012-05-01'
union all
select
'003', '0000000000', '2012-06-01'
union all
select
'003', '0000000000', '2012-08-01';
with tb as
(select ROW_NUMBER() over(PARTITION by col1 order by col1,col3) as rownum , col1,col2,col3 from test)
select col1,
isnull((select top 1 col3 from tb a where a.col1=b.col1 and a.rownum=b.rownum-1 ),convert(nvarchar(12),dateadd(month,-1,cast(col3 as datetime)),23)) as col2
,col3
from tb b
--(9 行受影响)
--col1 col2 col3
---------------------------------------------------- -------------------------------------------------- --------------------------------------------------
--001 2011-11-01 2011-12-01
--001 2011-12-01 2012-01-01
--001 2012-01-01 2012-08-01
--002 2011-10-01 2011-11-01
--002 2011-11-01 2011-12-01
--002 2011-12-01 2012-01-01
--003 2012-04-01 2012-05-01
--003 2012-05-01 2012-06-01
--003 2012-06-01 2012-08-01--(9 行受影响)
IF OBJECT_ID('tempdb.dbo.#a') IS NOT NULL
DROP TABLE #aCREATE TABLE #a([档案号] varchar(16),[上次抄表时间] varchar(16),[本次抄表时间] varchar(16))
INSERT INTO #a ( 档案号, 上次抄表时间, 本次抄表时间 )
SELECT '001','0000000000','2011-12-01' UNION ALL
SELECT '001','0000000000','2012-01-01' UNION ALL
SELECT '001','0000000000','2012-08-01' UNION ALL
SELECT '002','0000000000','2011-11-01' UNION ALL
SELECT '002','0000000000','2011-12-01' UNION ALL
SELECT '002','0000000000','2012-01-01' UNION ALL
SELECT '003','0000000000','2012-05-01' UNION ALL
SELECT '003','0000000000','2012-06-01' UNION ALL
SELECT '003','0000000000','2012-08-01'IF OBJECT_ID('tempdb.dbo.#B') IS NOT NULL
DROP TABLE #BSELECT B1.[档案号],ISNULL(B2.[本次抄表时间],CONVERT(varchar(10),DATEADD(MONTH,-1,B1.[本次抄表时间]),120)) [上次抄表时间],B1.[本次抄表时间]
INTO #B
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY [档案号] ORDER BY [档案号],[本次抄表时间]) Row,* FROM #A) B1 LEFT JOIN
(SELECT ROW_NUMBER() OVER(PARTITION BY [档案号] ORDER BY [档案号],[本次抄表时间]) Row,* FROM #A) B2 ON B1.Row = B2.Row +1 AND B1.[档案号] = B2.[档案号]UPDATE #a
SET [上次抄表时间] = B.[上次抄表时间]
FROM #a A INNER JOIN #B B ON A.档案号 = B.[档案号] AND A.[本次抄表时间] = B.[本次抄表时间] SELECT * FROM #a DROP TABLE #a
DROP TABLE #B
学习了!!!
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST; --> 如果表TEST不为空,删除表
GO
CREATE TABLE TEST --> 创建表,字段及类型如下
(
col1 nvarchar(50),
col2 nvarchar(50),
col3 nvarchar(50)
);
GO
insert into TEST
select
'001', '0000000000', '2011-12-01'
union all
select
'001', '0000000000', '2012-01-01'
union all
select
'001', '0000000000', '2012-08-01'
union all
select
'002', '0000000000', '2011-11-01'
union all
select
'002', '0000000000', '2011-12-01'
union all
select
'002', '0000000000', '2012-01-01'
union all
select
'003', '0000000000', '2012-05-01'
union all
select
'003', '0000000000', '2012-06-01'
union all
select
'003', '0000000000', '2012-08-01';
with tb as
(select ROW_NUMBER() over(PARTITION by col1 order by col1,col3) as rownum , col1,col2,col3 from test)
update test1 set col2=isnull((select top 1 b.col3 from tb a where a.col1=b.col1 and a.rownum=b.rownum-1 ),
convert(nvarchar(12),dateadd(month,-1,cast(b.col3 as datetime)),23)) from tb b ,TEST test1 where b.col1=TEST1.col1 and b.col3=test1.col3 --如果col1、col3能确定唯一行的话
select * from TEST
USE tempdb
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST; --> 如果表TEST不为空,删除表
GO
CREATE TABLE TEST --> 创建表,字段及类型如下
(
col1 nvarchar(50),
col2 nvarchar(50),
col3 nvarchar(50)
);
GO
insert into TEST
select
'001', '0000000000', '2011-12-01'
union all
select
'001', '0000000000', '2012-01-01'
union all
select
'001', '0000000000', '2012-08-01'
union all
select
'002', '0000000000', '2011-11-01'
union all
select
'002', '0000000000', '2011-12-01'
union all
select
'002', '0000000000', '2012-01-01'
union all
select
'003', '0000000000', '2012-05-01'
union all
select
'003', '0000000000', '2012-06-01'
union all
select
'003', '0000000000', '2012-08-01';
with tb as
(select ROW_NUMBER() over(PARTITION by col1 order by col1,col3) as rownum , col1,col2,col3 from test)
update test1 set col2=isnull((select top 1 a.col3 from tb a where a.col1=b.col1 and a.rownum=b.rownum-1 ),
convert(nvarchar(12),dateadd(month,-1,cast(b.col3 as datetime)),23)) from tb b ,TEST test1 where b.col1=TEST1.col1 and b.col3=test1.col3 --如果col1、col3能确定唯一行的话
select * from TEST