34,587
社区成员
发帖
与我相关
我的任务
分享
-------------------获取第二行数据记录-----------------------------
select top 1 * from tb where [primary_key] not in (select top 1 [primary_key] from tb)
--2005CTE 有顺序地加序号
WITH T1RN AS
(
SELECT col1, ROW_NUMBER() OVER(ORDER BY col2) AS RowNum
FROM dbo.T1
)
UPDATE T1RN SET col1 = RowNum;
-------------
select * from T1 order by col1
/*
col1 col2
----------- -----
1 A
2 A
3 A
4 B
5 B
6 C
7 C
8 C
9 C
10 C
*/
declare @i int
set @i=2
--2005
;with t as
(
select id=row_number(order by 排序字段),* from tb
)
update t set..... from t where id=@i
SQL2005
;with t as
(
select rn=row_number()over(order by getdate()),*
from tb
)
update t set col=@col where rn=2
declare @a int
set @a=2
update tb set col=@a where id=2
?我晕,那没办法,
加个自增列来吧
SELECT IDD=IDENTITY(INT,1,1),* INTO #T FROM TB
UPDATE #T SET COL=... WHERE IDD=你想要的行
-- sql 2000
update tb
set ...
from tb m,
(select * , px = (select count(1) from tb where id < t.id) + 1 from tb t) n
where m.id = n.id and n.px = 2
-- sql 2005
update tb
set ...
from tb m,
(select * , px = row_number() over(order by id) from tb t) n
where m.id = n.id and n.px = 2