27,579
社区成员
发帖
与我相关
我的任务
分享
with t as(
select *,rn=row_number() over(partition by 分组列 order by 排序列)from tb
)
update t
set 字段4=rn
IF OBJECT_ID('[MyTest]') IS NOT NULL
DROP TABLE [MyTest]
GO
CREATE TABLE [dbo].[MyTest](
[ID] [int] NOT NULL,
[ParentID] [int] NULL,
[Name] [nvarchar](50) NULL
) ON [PRIMARY]
INSERT INTO MyTest ([ID], [ParentID], [Name]) VALUES (1, 0, 'A')
INSERT INTO MyTest ([ID], [ParentID], [Name]) VALUES (2, 1, 'B')
INSERT INTO MyTest ([ID], [ParentID], [Name]) VALUES (3, 1, 'C')
INSERT INTO MyTest ([ID], [ParentID], [Name]) VALUES (4, 1, 'D')
INSERT INTO MyTest ([ID], [ParentID], [Name]) VALUES (5, 2, 'E')
INSERT INTO MyTest ([ID], [ParentID], [Name]) VALUES (6, 3, 'F')
INSERT INTO MyTest ([ID], [ParentID], [Name]) VALUES (7, 5, 'G')
select *,字段4=ROW_NUMBER()over(order by getdate()) from [MyTest] where [ParentID]=0
/*
ID ParentID Name 字段4
----------- ----------- -------------------------------------------------- --------------------
1 0 A 1
(1 行受影响)
*/
select *,字段4=ROW_NUMBER()over(order by getdate()) from [MyTest] where [ParentID]=1
/*
ID ParentID Name 字段4
----------- ----------- -------------------------------------------------- --------------------
2 1 B 1
3 1 C 2
4 1 D 3
(3 行受影响)
*/
这种效果吧--更正
if not object_id('tb') is null
drop table tb
Go
Create table tb([字段1] nvarchar(1),[字段2] nvarchar(1),[字段3] nvarchar(1),[字段4] int)
Insert tb
select N'a',N'b',N'c',1 union all
select N'd',N'e',N'f',2 union all
select N'g',N'h',N'i',3 union all
select N'j',N'k',N'l',4
Go
select 字段1,
字段2,
字段3,
字段4=(select count(*) from tb where [字段1] in('a','j') and 字段4<=t.字段4)
from tb t
where [字段1] in('a','j')
/*
字段1 字段2 字段3 字段4
---- ---- ---- -----------
a b c 1
j k l 2
(2 個資料列受到影響)
*/
select 字段1,
字段2,
字段3,
字段4=(select count(*) from tb where 字段4<t.字段4)
from tb t
where...