update tb set name='asdfasdf' where sktime=(select max(sktime) from tb)
.... where sktime='时间最近的'
;with tb as ( select top 1 * from tbname order by sktime desc ) update tb set xx=''
--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([Card_dateTime] [datetime],[CardNo] [nvarchar](10),[Mc_ID] [nvarchar](10)) INSERT INTO [tb] SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL SELECT '2010-05-28 16:16:00','0002001001','08' UNION ALL SELECT '2010-05-28 20:33:00','0002002041','08' -->SQL查询如下: ;WITH t AS ( SELECT rn = ROW_NUMBER()OVER(ORDER BY [Card_dateTime] DESC), * FROM tb ) UPDATE t SET Mc_Id = '09' WHERE rn = 1SELECT * FROM [tb] /* Card_dateTime CardNo Mc_ID ----------------------- ---------- ---------- 2010-05-28 16:15:00.000 0002001001 08 2010-05-28 16:15:00.000 0002001001 08 2010-05-28 16:16:00.000 0002001001 08 2010-05-28 20:33:00.000 0002002041 09(4 行受影响) */
这位兄弟提醒了我,我想复杂了。--> 生成测试数据表: [tb] IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb] GO CREATE TABLE [tb] ([Card_dateTime] [datetime],[CardNo] [nvarchar](10),[Mc_ID] [nvarchar](10)) INSERT INTO [tb] SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL SELECT '2010-05-28 16:16:00','0002001001','08' UNION ALL SELECT '2010-05-28 20:33:00','0002002041','08'-->SQL查询如下: UPDATE t SET Mc_Id = '09' FROM ( SELECT TOP 1* FROM tb ORDER BY 1 DESC ) t SELECT * FROM [tb] /* Card_dateTime CardNo Mc_ID ----------------------- ---------- ---------- 2010-05-28 16:15:00.000 0002001001 08 2010-05-28 16:15:00.000 0002001001 08 2010-05-28 16:16:00.000 0002001001 08 2010-05-28 20:33:00.000 0002002041 09(4 行受影响) */不够不支持SQL2000的。
where sktime=(select max(sktime) from tb)
.... where sktime='时间最近的'
(
select top 1 * from tbname order by sktime desc
)
update tb
set xx=''
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Card_dateTime] [datetime],[CardNo] [nvarchar](10),[Mc_ID] [nvarchar](10))
INSERT INTO [tb]
SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL
SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL
SELECT '2010-05-28 16:16:00','0002001001','08' UNION ALL
SELECT '2010-05-28 20:33:00','0002002041','08'
-->SQL查询如下:
;WITH t AS
(
SELECT rn = ROW_NUMBER()OVER(ORDER BY [Card_dateTime] DESC), *
FROM tb
)
UPDATE t
SET Mc_Id = '09'
WHERE rn = 1SELECT * FROM [tb]
/*
Card_dateTime CardNo Mc_ID
----------------------- ---------- ----------
2010-05-28 16:15:00.000 0002001001 08
2010-05-28 16:15:00.000 0002001001 08
2010-05-28 16:16:00.000 0002001001 08
2010-05-28 20:33:00.000 0002002041 09(4 行受影响)
*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([Card_dateTime] [datetime],[CardNo] [nvarchar](10),[Mc_ID] [nvarchar](10))
INSERT INTO [tb]
SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL
SELECT '2010-05-28 16:15:00','0002001001','08' UNION ALL
SELECT '2010-05-28 16:16:00','0002001001','08' UNION ALL
SELECT '2010-05-28 20:33:00','0002002041','08'-->SQL查询如下:
UPDATE t
SET Mc_Id = '09'
FROM (
SELECT TOP 1*
FROM tb
ORDER BY 1 DESC
) t
SELECT * FROM [tb]
/*
Card_dateTime CardNo Mc_ID
----------------------- ---------- ----------
2010-05-28 16:15:00.000 0002001001 08
2010-05-28 16:15:00.000 0002001001 08
2010-05-28 16:16:00.000 0002001001 08
2010-05-28 20:33:00.000 0002002041 09(4 行受影响)
*/不够不支持SQL2000的。
(
select top 1 * from tbname where skA=a and skB=b and skC=c order by sktime desc
)
update tb
set xx=''是这样吗?
set -----
where sktime=(select top 1 sktime from t order by sktime desc)
set f1='v1',f2='v2'
where sktime=(select max(sktime) from t)
and ska='a'
and skb='b'
where sktime=(select top 1 sktime from tablename order by sktime desc)
update t set name='123' where sktime = (select top 1 sktime from t order by sktime desc);