UPDATE T SET A2= (SELECT TOP 1 A1 FROM TEST WHERE KID=T.KID AND THEDATE<T.THEDATE ORDER BY THEDATE DESC) FROM TEST T
create table test(id int, kid int, a1 int, a2 int, thedate smalldatetime) goinsert into test values(1,2,3,4,getdate())select * from testupdate test set a2=kid,a1=a2select * from test/*id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 2 3 4 2010-04-14 15:46:00id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 2 4 2 2010-04-14 15:46:00*/ --drop table test
update tb set a1=a2 where kid=guestId(传入参数)
--这样? ---------------------------------------------------------------- -- Author :fredrickhu(小F,向高手学习) -- Date :2010-04-14 15:52:02 -- Verstion: -- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86) -- May 26 2009 14:24:20 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3) -- ---------------------------------------------------------------- --> 测试数据:[test] if object_id('[test]') is not null drop table [test] go create table [test]([id] int,[kid] int,[a1] int,[a2] int,[thedate] datetime) insert [test] select 1,1,1,1,'2010-4-11' union all select 2,1,2,1,'2010-4-12' union all select 3,2,2,1,'2010-4-13' union all select 4,3,3,1,'2010-4-14' --------------开始查询-------------------------- update a set a2=b.a1 from test a,test b where a.kid=b.kid and datediff(dd,a.thedate,b.thedate)=1select * from test ----------------结果---------------------------- /* id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 1 1 2 2010-04-11 00:00:00.000 2 1 2 1 2010-04-12 00:00:00.000 3 2 2 1 2010-04-13 00:00:00.000 4 3 3 1 2010-04-14 00:00:00.000(4 行受影响) */
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-14 16:05:35 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/ --> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime) INSERT [tb] SELECT 1,1,1,N'2010-04-13' UNION ALL SELECT 2,2,2,N'2010-04-13' UNION ALL SELECT 3,1,2,N'2010-04-14' UNION ALL SELECT 4,2,3,N'2010-04-14' GO --SELECT * FROM [tb] --想在当天去更新a2的值,当kid相同的时候,去把昨天的a1的数据更新到a2 -->SQL查询如下: update t set a2=isnull((select top 1 a2 from tb where a1=t.a1 and thedate<t.thedate order by thedate),a2) from tb tselect * from tb /* id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 1 1 1 2010-04-13 00:00:00.000 2 2 2 2 2010-04-13 00:00:00.000 3 3 1 1 2010-04-14 00:00:00.000 4 4 2 2 2010-04-14 00:00:00.000(4 行受影响) */
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-14 16:05:35 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/ --> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime) INSERT [tb] SELECT 1,1,1,N'2010-04-13' UNION ALL SELECT 2,2,2,N'2010-04-13' UNION ALL SELECT 3,1,2,N'2010-04-14' UNION ALL SELECT 4,2,3,N'2010-04-14' UNION ALL SELECT 3,1,6,N'2010-04-15' UNION ALL SELECT 4,2,7,N'2010-04-15' GO --SELECT * FROM [tb] --想在当天去更新a2的值,当kid相同的时候,去把昨天的a1的数据更新到a2 -->SQL查询如下: update t set a2=isnull((select top 1 a2 from tb where a1=t.a1 and thedate<t.thedate order by thedate desc),a2) from tb tselect * from tb /* id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 1 1 1 2010-04-13 00:00:00.000 2 2 2 2 2010-04-13 00:00:00.000 3 3 1 1 2010-04-14 00:00:00.000 4 4 2 2 2010-04-14 00:00:00.000 5 3 1 2 2010-04-15 00:00:00.000 6 4 2 3 2010-04-15 00:00:00.000(6 行受影响) */还是要这个结果
update t set a2=isnull((select top 1 a1 from tb where kid=t.kid and thedate>t.thedate order by thedate asc),a2) from tb tselect * from tb
update a set a.[a2]=b.a1 from [tb] a,[tb] b where datediff(day,a.thedate,getdate())=0 and datediff(day,b.thedate,a.thedate)=1 and a.kid=b.kid id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 1 1 1 2010-04-13 00:00:00.000 2 2 2 2 2010-04-13 00:00:00.000 3 1 3 1 2010-04-14 00:00:00.000 4 2 4 2 2010-04-14 00:00:00.000(4 行受影响)这样。
/*------------------------------------------------------------------ -- Author : htl258(Tony) -- Date : 2010-04-14 16:05:35 -- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) Jul 9 2008 14:43:34 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/ --> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime) INSERT [tb] SELECT 1,1,1,N'2010-04-13' UNION ALL SELECT 2,2,2,N'2010-04-13' UNION ALL SELECT 3,1,2,N'2010-04-14' UNION ALL SELECT 4,2,3,N'2010-04-14' UNION ALL SELECT 3,1,6,N'2010-04-15' UNION ALL SELECT 4,2,7,N'2010-04-15' GO --SELECT * FROM [tb] --想在当天去更新a2的值,当kid相同的时候,去把昨天的a1的数据更新到a2 -->SQL查询如下: update t set a2=(select top 1 a2 from tb where a1=t.a1 and thedate<t.thedate order by thedate desc) from tb tselect * from tb /* id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 1 1 NULL 2010-04-13 00:00:00.000 2 2 2 NULL 2010-04-13 00:00:00.000 3 3 1 1 2010-04-14 00:00:00.000 4 4 2 2 2010-04-14 00:00:00.000 5 3 1 2 2010-04-15 00:00:00.000 6 4 2 3 2010-04-15 00:00:00.000(6 行受影响) */
好像这样可以实现了,多谢各位 update t set a2=isnull((select top 1 a1 from tb where kid=t.kid and t.thedate>thedate order by id desc),a2) from tb tselect * from tb
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime) INSERT [tb] SELECT 1,1,null,N'2010-04-13' UNION ALL SELECT 2,2,null,N'2010-04-13' UNION ALL SELECT 1,2,null,N'2010-04-14' UNION ALL SELECT 2,3,null,N'2010-04-14' GO select * from tb /* id kid a1 a2 thedate ----------- ----------- ----------- ----------- ----------------------- 1 1 1 2010-04-13 00:00:00.000 2 2 2 2010-04-13 00:00:00.000 3 1 3 1 2010-04-14 00:00:00.000 4 2 2 2 2010-04-14 00:00:00.000(4 行受影响) */update t set a2=(select top 1 a1 from tb where kid=t.kid and thedate=dateadd(dd,-1,t.thedate) order by thedate) from tb tselect * from tb (所影响的行数为 4 行)id kid a1 a2 thedate ----------- ----------- ----------- ----------- ------------------------------------------------------ 1 1 1 NULL 2010-04-13 00:00:00.000 2 2 2 NULL 2010-04-13 00:00:00.000 3 1 2 NULL 2010-04-14 00:00:00.000 4 2 3 NULL 2010-04-14 00:00:00.000(所影响的行数为 4 行) (所影响的行数为 4 行)id kid a1 a2 thedate ----------- ----------- ----------- ----------- ------------------------------------------------------ 1 1 1 NULL 2010-04-13 00:00:00.000 2 2 2 NULL 2010-04-13 00:00:00.000 3 1 2 1 2010-04-14 00:00:00.000 4 2 3 2 2010-04-14 00:00:00.000(所影响的行数为 4 行)
(SELECT TOP 1 A1 FROM TEST WHERE KID=T.KID AND THEDATE<T.THEDATE ORDER BY THEDATE DESC)
FROM TEST T
kid int,
a1 int,
a2 int,
thedate smalldatetime)
goinsert into test values(1,2,3,4,getdate())select * from testupdate test set a2=kid,a1=a2select * from test/*id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 2 3 4 2010-04-14 15:46:00id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 2 4 2 2010-04-14 15:46:00*/
--drop table test
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-04-14 15:52:02
-- Verstion:
-- Microsoft SQL Server 2005 - 9.00.4053.00 (Intel X86)
-- May 26 2009 14:24:20
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
go
create table [test]([id] int,[kid] int,[a1] int,[a2] int,[thedate] datetime)
insert [test]
select 1,1,1,1,'2010-4-11' union all
select 2,1,2,1,'2010-4-12' union all
select 3,2,2,1,'2010-4-13' union all
select 4,3,3,1,'2010-4-14'
--------------开始查询--------------------------
update
a
set
a2=b.a1
from
test a,test b
where
a.kid=b.kid
and
datediff(dd,a.thedate,b.thedate)=1select * from test
----------------结果----------------------------
/* id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 2 2010-04-11 00:00:00.000
2 1 2 1 2010-04-12 00:00:00.000
3 2 2 1 2010-04-13 00:00:00.000
4 3 3 1 2010-04-14 00:00:00.000(4 行受影响)
*/
----------------数据----------------------------
/* id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 2010-04-13 00:00:00.000
2 2 2 2010-04-13 00:00:00.000
3 1 2 2010-04-14 00:00:00.000
4 2 3 2010-04-14 00:00:00.000这样的数据,想在当天去更新a2的值,当kid相同的时候,去把昨天的a1的数据更新到a2
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-14 16:05:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime)
INSERT [tb]
SELECT 1,1,1,N'2010-04-13' UNION ALL
SELECT 2,2,2,N'2010-04-13' UNION ALL
SELECT 3,1,2,N'2010-04-14' UNION ALL
SELECT 4,2,3,N'2010-04-14'
GO
--SELECT * FROM [tb]
--想在当天去更新a2的值,当kid相同的时候,去把昨天的a1的数据更新到a2
-->SQL查询如下:
update t set
a2=isnull((select top 1 a2 from tb where a1=t.a1 and thedate<t.thedate order by thedate),a2)
from tb tselect * from tb
/*
id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 1 2010-04-13 00:00:00.000
2 2 2 2 2010-04-13 00:00:00.000
3 3 1 1 2010-04-14 00:00:00.000
4 4 2 2 2010-04-14 00:00:00.000(4 行受影响)
*/
/*------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-14 16:05:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime)
INSERT [tb]
SELECT 1,1,1,N'2010-04-13' UNION ALL
SELECT 2,2,2,N'2010-04-13' UNION ALL
SELECT 3,1,2,N'2010-04-14' UNION ALL
SELECT 4,2,3,N'2010-04-14' UNION ALL
SELECT 3,1,6,N'2010-04-15' UNION ALL
SELECT 4,2,7,N'2010-04-15'
GO
--SELECT * FROM [tb]
--想在当天去更新a2的值,当kid相同的时候,去把昨天的a1的数据更新到a2
-->SQL查询如下:
update t set
a2=isnull((select top 1 a2 from tb where a1=t.a1 and thedate<t.thedate order by thedate desc),a2)
from tb tselect * from tb
/*
id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 1 2010-04-13 00:00:00.000
2 2 2 2 2010-04-13 00:00:00.000
3 3 1 1 2010-04-14 00:00:00.000
4 4 2 2 2010-04-14 00:00:00.000
5 3 1 2 2010-04-15 00:00:00.000
6 4 2 3 2010-04-15 00:00:00.000(6 行受影响)
*/还是要这个结果
a2=isnull((select top 1 a1 from tb where kid=t.kid and thedate>t.thedate order by thedate asc),a2)
from tb tselect * from tb
set a.[a2]=b.a1
from [tb] a,[tb] b
where datediff(day,a.thedate,getdate())=0 and datediff(day,b.thedate,a.thedate)=1 and a.kid=b.kid id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 1 2010-04-13 00:00:00.000
2 2 2 2 2010-04-13 00:00:00.000
3 1 3 1 2010-04-14 00:00:00.000
4 2 4 2 2010-04-14 00:00:00.000(4 行受影响)这样。
*
id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 2010-04-13 00:00:00.000
2 2 2 2010-04-13 00:00:00.000
3 1 3 1 2010-04-14 00:00:00.000
4 2 2 2 2010-04-14 00:00:00.000(4 行受影响)
*/
-- Author : htl258(Tony)
-- Date : 2010-04-14 16:05:35
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)------------------------------------------------------------------*/
--> 生成测试数据表:tbIF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime)
INSERT [tb]
SELECT 1,1,1,N'2010-04-13' UNION ALL
SELECT 2,2,2,N'2010-04-13' UNION ALL
SELECT 3,1,2,N'2010-04-14' UNION ALL
SELECT 4,2,3,N'2010-04-14' UNION ALL
SELECT 3,1,6,N'2010-04-15' UNION ALL
SELECT 4,2,7,N'2010-04-15'
GO
--SELECT * FROM [tb]
--想在当天去更新a2的值,当kid相同的时候,去把昨天的a1的数据更新到a2
-->SQL查询如下:
update t set
a2=(select top 1 a2 from tb where a1=t.a1 and thedate<t.thedate order by thedate desc)
from tb tselect * from tb
/*
id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 NULL 2010-04-13 00:00:00.000
2 2 2 NULL 2010-04-13 00:00:00.000
3 3 1 1 2010-04-14 00:00:00.000
4 4 2 2 2010-04-14 00:00:00.000
5 3 1 2 2010-04-15 00:00:00.000
6 4 2 3 2010-04-15 00:00:00.000(6 行受影响)
*/
update t set
a2=isnull((select top 1 a1 from tb where kid=t.kid and t.thedate>thedate order by id desc),a2)
from tb tselect * from tb
DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT IDENTITY,[kid] INT,[a1] INT,[a2] INT,thedate datetime)
INSERT [tb]
SELECT 1,1,null,N'2010-04-13' UNION ALL
SELECT 2,2,null,N'2010-04-13' UNION ALL
SELECT 1,2,null,N'2010-04-14' UNION ALL
SELECT 2,3,null,N'2010-04-14'
GO
select * from tb
/*
id kid a1 a2 thedate
----------- ----------- ----------- ----------- -----------------------
1 1 1 2010-04-13 00:00:00.000
2 2 2 2010-04-13 00:00:00.000
3 1 3 1 2010-04-14 00:00:00.000
4 2 2 2 2010-04-14 00:00:00.000(4 行受影响)
*/update t set
a2=(select top 1 a1 from tb where kid=t.kid and thedate=dateadd(dd,-1,t.thedate) order by thedate)
from tb tselect * from tb
(所影响的行数为 4 行)id kid a1 a2 thedate
----------- ----------- ----------- ----------- ------------------------------------------------------
1 1 1 NULL 2010-04-13 00:00:00.000
2 2 2 NULL 2010-04-13 00:00:00.000
3 1 2 NULL 2010-04-14 00:00:00.000
4 2 3 NULL 2010-04-14 00:00:00.000(所影响的行数为 4 行)
(所影响的行数为 4 行)id kid a1 a2 thedate
----------- ----------- ----------- ----------- ------------------------------------------------------
1 1 1 NULL 2010-04-13 00:00:00.000
2 2 2 NULL 2010-04-13 00:00:00.000
3 1 2 1 2010-04-14 00:00:00.000
4 2 3 2 2010-04-14 00:00:00.000(所影响的行数为 4 行)