27,579
社区成员
发帖
与我相关
我的任务
分享给表中字段分配顺序ID
表格如下：
name ID
A
A
A
A
A
B
B
B
B
B
B
C
C
C
C
C
C
C
要得到的结果
name ID
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
B 6
C 1
C 2
C 3
C 4
C 5
C 6
C 7
在一个表中，依据name列的不同，分别分配顺序的ID号。尽量不要用游标来实现，可以么？
name ID
A
A
A
A
A
B
B
B
B
B
B
C
C
C
C
C
C
C
要得到的结果
name ID
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
B 6
C 1
C 2
C 3
C 4
C 5
C 6
C 7
在一个表中，依据name列的不同，分别分配顺序的ID号。尽量不要用游标来实现，可以么？
...全文
请发表友善的回复…
发表回复
luoxi1124 2009-09-17
- 打赏
- 举报
[Quote=引用 3 楼 sgtzzc 的回复:]
SQL codeselect name,id=identity(int,1,1)into #from tbselect*from #droptable #
[/Quote]
这个好像不能实现我要的结果。。。
SQL codeselect name,id=identity(int,1,1)into #from tbselect*from #droptable #
[/Quote]
这个好像不能实现我要的结果。。。
luoxi1124 2009-09-17
- 打赏
- 举报
[Quote=引用 2 楼 xiequan2 的回复:]
row_number() over(partition by name order by name)
[/Quote]
这个是SQL2005里面的方法吧，可是我用的是SQL2000
row_number() over(partition by name order by name)
[/Quote]
这个是SQL2005里面的方法吧，可是我用的是SQL2000
luoxi1124 2009-09-17
- 打赏
- 举报
[Quote=引用 1 楼 htl258 的回复:]
可以，2000 or 2005
[/Quote]
我用的是SQL2000
可以，2000 or 2005
[/Quote]
我用的是SQL2000
htl258_Tony 2009-09-16
- 打赏
- 举报
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-16 17:21:22
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] NVARCHAR(10),[ID] INT)
INSERT [tb]
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL
GO
--SELECT * FROM [tb]
-->SQL查询如下:
DECLARE @i INT,@j NVARCHAR(20)
UPDATE tb SET id=@i,@i=CASE WHEN NAME<>@j THEN 1 ELSE ISNULL(@i,0)+1 END,@j=NAME
SELECT * FROM tb
/*
name ID
---------- ----------
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
B 6
C 1
C 2
C 3
C 4
C 5
C 6
C 7
(18 行受影响)
*/htl258_Tony 2009-09-16
- 打赏
- 举报
/*---------------------------------
-- Author : htl258(Tony)
-- Date : 2009-09-16 17:21:22
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
Mar 29 2009 10:27:29
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
---------------------------------*/
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([name] NVARCHAR(10),[ID] NVARCHAR(10))
INSERT [tb]
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'A',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'B',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL UNION ALL
SELECT 'C',NULL
GO
--SELECT * FROM [tb]
-->SQL查询如下:
;WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY GETDATE()),*
FROM tb
)
UPDATE t SET id=rn
SELECT * FROM tb
/*
name ID
---------- ----------
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
B 6
C 1
C 2
C 3
C 4
C 5
C 6
C 7
(18 行受影响)
*/
--2000
ALTER TABLE tb ADD rn INT IDENTITY
GO
UPDATE tb SET
id=(SELECT COUNT(1) FROM tb t WHERE t.NAME=tb.NAME AND t.rn<=tb.rn)
ALTER TABLE tb DROP COLUMN rn
GO
SELECT * FROM tb
/*
name ID
---------- ----------
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
B 6
C 1
C 2
C 3
C 4
C 5
C 6
C 7
(18 行受影响)
*/
lunzi028 2009-09-16
- 打赏
- 举报
DECLARE @TABLE TABLE (CNAME CHAR(50))
INSERT INTO @TABLE
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT 'B'
SELECT * FROM @TABLE
SELECT CNAME,row_number() over(partition by cname order by cname ) FROM @TABLE
--小F-- 2009-09-16
- 打赏
- 举报
----------------------------------------------------------------
-- Author :fredrickhu(我是小F，向高手学习)
-- Date :2009-09-16 17:23:00
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1))
insert [tb]
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'A' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'B' union all
select 'C' union all
select 'C' union all
select 'C' union all
select 'C' union all
select 'C' union all
select 'C' union all
select 'C'
--------------开始查询--------------------------
select name,id= row_number() over(partition by name order by name) from [tb]
----------------结果----------------------------
/*name id
---- --------------------
A 1
A 2
A 3
A 4
A 5
B 1
B 2
B 3
B 4
B 5
B 6
C 1
C 2
C 3
C 4
C 5
C 6
C 7
(18 行受影响)
*/
sgtzzc 2009-09-16
- 打赏
- 举报
[Quote=引用 3 楼 sgtzzc 的回复:]
SQL codeselect name,id=identity(int,1,1)into #from tbselect*from #droptable #
[/Quote]
改改
SQL codeselect name,id=identity(int,1,1)into #from tbselect*from #droptable #
[/Quote]
改改
select name,id=identity(int,1,1) into # from tb
select name,id=(select count(1)+1 from # where name=t.name and id<t.id) from # t
drop table #
--小F-- 2009-09-16
- 打赏
- 举报
排名函数
sgtzzc 2009-09-16
- 打赏
- 举报
select name,id=identity(int,1,1) into # from tb
select * from #
drop table #
xiequan2 2009-09-16
- 打赏
- 举报
row_number() over(partition by name order by name)
htl258_Tony 2009-09-16
- 打赏
- 举报
可以，2000 or 2005
