要求:
一个表如下表1,用SQL 语句输出如下表2结果(即按GID分组,按Ikey排序后,为每组加一个index列,且index要从1开始)
表1:
GID IKey
3 a
3 b
3 d
4 b
4 a
5 a
5 c
5 d
5 b
....
输出,表2
GID Ikey index
3 a 1
3 b 2
3 d 3
4 a 1
4 b 2
5 a 1
5 b 2
5 c 3
5 d 4
...
一个表如下表1,用SQL 语句输出如下表2结果(即按GID分组,按Ikey排序后,为每组加一个index列,且index要从1开始)
表1:
GID IKey
3 a
3 b
3 d
4 b
4 a
5 a
5 c
5 d
5 b
....
输出,表2
GID Ikey index
3 a 1
3 b 2
3 d 3
4 a 1
4 b 2
5 a 1
5 b 2
5 c 3
5 d 4
...
insert into tabl
select 3 ,'a' union all
select 3,'b' union all
select 3,'c' union all
select 4,'b' union all
select 4,'a' union all
select 5,'a' union all
select 5,'c' union all
select 5,'d'
------------------
CREATE TABLE #3(Ind int,name varchar(10),id int)--结果表
CREATE TABLE #2(ids int IDENTITY(1,1),name varchar(10))--临时表
--用游标
DECLARE cour CURSOR
FOR SELECT DISTINCT Ind FROM tabl
DECLARE @iname int
OPEN cour
FETCH NEXT FROM cour INTO @iname
WHILE @@fetch_status = 0
BEGIN
INSERT into #2
select name
from tabl
WHERE Ind=@iname
order BY name
INSERT INTO #3 SELECT @iname, name,ids FROM #2
TRUNCATE TABLE #2
FETCH NEXT FROM cour INTO @iname
END
DEALLOCATE courselect * from #3
---------------------------
方法用到临时表,游标,比较麻烦,不知大家有什么其他好方法?
insert into #temp_tabl
select 3 ,'a' union all
select 3,'b' union all
select 3,'c' union all
select 4,'b' union all
select 4,'a' union all
select 5,'a' union all
select 5,'c' union all
select 5,'d' select
gid
,ikey
,row_number()over(partition by gid order by ikey)as idx
from
#temp_tabl
order by gid,idxdrop table #temp_tabl
-->Author:wufeng4552【水族杰纶】
-->Date :2009-08-26 07:57:36
declare @T table([GID] int,[IKey] nvarchar(1))
Insert @T
select 3,N'a' union all
select 3,N'b' union all
select 3,N'd' union all
select 4,N'b' union all
select 4,N'a' union all
select 5,N'a' union all
select 5,N'c' union all
select 5,N'd' union all
select 5,N'b'
--2000
Select * ,[index]=(select count(*) from @t where Gid=t.GID and Ikey<=t.IKEY)from @T t
order by GID,Ikey
--2005
Select * ,[index]=row_number()over (partition by gid order by ikey)from @T t
order by GID,Ikey
/*
GID IKey index
----------- ---- -----------
3 a 1
3 b 2
3 d 3
4 a 1
4 b 2
5 a 1
5 b 2
5 c 3
5 d 4(9 個資料列受到影響)
*/
insert into tabl
select 3 ,'a' union all
select 3,'b' union all
select 3,'c' union all
select 4,'b' union all
select 4,'a' union all
select 5,'a' union all
select 5,'c' union all
select 5,'d'
--2000
select a.*,id=(select count(1) from tabl where Ind=a.Ind and name<=a.name ) from tabl a order by ind,name
/*
Ind name id
----------- ----- -----------
3 a 1
3 b 2
3 c 3
4 a 1
4 b 2
5 a 1
5 c 2
5 d 3(8 行受影响)
*/
我还是赞成用Row_number Over()函数