有一张表
CategoryID MemberID Number
9 1135
9 5512
9 83
9 61
9 52
9 61
11 123
11 89
11 11
11 76
14 32
14 989 按照Category自动分配Number,
distinct(CategoryID,MemberID)分配同一个号码
在同一个category中从1排到n 输出如表 CategoryID MemberID Number
9 1135 1
9 5512 2
9 83 3
9 61 4
9 52 5
9 61 4
11 123 1
11 89 2
11 11 3
11 76 4
14 32 1
14 989 2请问怎么写这个存储过程,是否要用到2个游标呢?
CategoryID MemberID Number
9 1135
9 5512
9 83
9 61
9 52
9 61
11 123
11 89
11 11
11 76
14 32
14 989 按照Category自动分配Number,
distinct(CategoryID,MemberID)分配同一个号码
在同一个category中从1排到n 输出如表 CategoryID MemberID Number
9 1135 1
9 5512 2
9 83 3
9 61 4
9 52 5
9 61 4
11 123 1
11 89 2
11 11 3
11 76 4
14 32 1
14 989 2请问怎么写这个存储过程,是否要用到2个游标呢?
解决方案 »
- 急呀!!!!!卸载sql server 2000时c盘下的sql server configuration manager用删除么??
- 进入sql2005时它要服务器名称,从哪来的?急啊 大家帮帮忙
- 如何查询一个记录数目,然后赋给一个变量。(除了用游标以外)
- 在线等!急救
- 怎样根据某列的最小值取数据?
- 表与表之间数据赋值问题,高手帮忙看哈!
- 如何判断一个用户是否有权限访问一个表?
- sqlserver从控制台中删除服务器后怎么恢复
- 关于SQL数据库的设计问题
- 请教,将一个数据表用ADO存为XML文件后,能否对XML文件中的记录进行分析汇总 ?
- 问个简单的小问题?
- 用嵌入式sql连接到sql server本地服务器的时候程序崩溃了
update 表
set Number = (select count(1) from 表 b where a.id=b.id and a.MemberID<b.MemberID)
from 表 a
select CategoryID,MemberID,
Number= RANK() OVER (PARTITION BY CategoryID order by MemberID)
from tb
9 52 5
9 61 4 没看懂...
我用的是sql2008,每一个category从1开始排到结束,然后第二个category再从一开始排
Select CategoryID,MemberID,
row_number() over(partition by CategoryID order by MemberID desc)as number
From t
drop table tb
go
create table tb(CategoryID int, MemberID int, Number int)
insert tb select 9,1135,null
insert tb select 9,5512,null
insert tb select 9,83,null
insert tb select 9,61,null
insert tb select 9,52,null
insert tb select 9,61,null
insert tb select 11,123,null
insert tb select 11,89,null
insert tb select 11,11,null
insert tb select 11,76,null
insert tb select 14,32,null
insert tb select 14,989,null
go
declare @i int,@j int
update tb set number=@i,@i=case when CategoryID=@j then isnull(@i+1,0) else 1 end,@j=CategoryIDselect * from tb/*
CategoryID MemberID Number
----------- ----------- -----------
9 1135 1
9 5512 2
9 83 3
9 61 4
9 52 5
9 61 6
11 123 1
11 89 2
11 11 3
11 76 4
14 32 1
14 989 2(12 行受影响)*/
注意,CategoryID =9中,MemberId=61有2条记录,所以分配同一个号
如果有重复记录10条的话,那么下一条不重复的number就会加上10,我想要连续的号,
除得用游标吗?能不能先把distinct(MemberID)选出来放到临时表上,然后用游标指着
updateCategoryID MemberID Number
9 1135 1
9 5512 2
9 83 3
9 61 4
9 61 4
9 52 6 11 123 1
11 89 2
11 11 3
11 76 4 14 32 1
14 989 2
drop table tb
go
create table tb(CategoryID int, MemberID int, Number int)
insert tb select 9,1135,null
insert tb select 9,5512,null
insert tb select 9,83,null
insert tb select 9,61,null
insert tb select 9,52,null
insert tb select 9,61,null
insert tb select 11,123,null
insert tb select 11,89,null
insert tb select 11,11,null
insert tb select 11,76,null
insert tb select 14,32,null
insert tb select 14,989,nullselect CategoryID,MemberID,number= RANK() OVER (PARTITION BY CategoryID order by MemberID desc)
from tb
CategoryID MemberID number
----------- ----------- --------------------
9 5512 1
9 1135 2
9 83 3
9 61 4
9 61 4
9 52 6
11 123 1
11 89 2
11 76 3
11 11 4
14 989 1
14 32 2(12 行受影响)
drop table tb
go
create table tb(CategoryID int, MemberID int, Number int)
insert tb select 9,1135,null
insert tb select 9,5512,null
insert tb select 9,83,null
insert tb select 9,61,null
insert tb select 9,52,null
insert tb select 9,61,null
insert tb select 11,123,null
insert tb select 11,89,null
insert tb select 11,11,null
insert tb select 11,76,null
insert tb select 14,32,null
insert tb select 14,989,nullselect CategoryID,MemberID,number=DENSE_RANK() OVER (PARTITION BY CategoryID order by MemberID desc)
from tb
CategoryID MemberID number
----------- ----------- --------------------
9 5512 1
9 1135 2
9 83 3
9 61 4
9 61 4
9 52 5
11 123 1
11 89 2
11 76 3
11 11 4
14 989 1
14 32 2(12 行受影响)
CategoryID int, MemberID int, Number int)
insert into @tab
select 9 , 1135 ,0 union all
select 9 , 5512 ,0 union all
select 9 , 83 ,0 union all
select 9 , 61 ,0 union all
select 9 , 52 ,0 union all
select 9 , 61 ,0 union all
select 11 , 123 ,0 union all
select 11 , 89 ,0 union all
select 11 , 11 ,0 union all
select 11 , 76 ,0 union all
select 14 , 32 ,0 union all
select 14 , 989 ,0
SELECT CategoryID,MemberID,DENSE_RANK()over (PARTITION BY CategoryID order by MemberID desc) AS Orderid
FROM @tab/*
CategoryID MemberID Orderid
----------- ----------- --------------------
9 5512 1
9 1135 2
9 83 3
9 61 4
9 61 4
9 52 5
11 123 1
11 89 2
11 76 3
11 11 4
14 989 1
14 32 2(12 行受影响)
*/
Create table T(CategoryID int, MemberID int, Number int)
insert into T (CategoryID,MemberID)
select 9 , 1135 union all
select 9 , 5512 union all
select 9 , 83 union all
select 9 , 61 union all
select 9 , 52 union all
select 9 , 61 union all
select 11 , 123 union all
select 11 , 89 union all
select 11 , 11 union all
select 11 , 76 union all
select 14 , 32 union all
select 14 , 989
GOselect *,TM=9999999
into #T
from Tdeclare @tm int,@CategoryID int
update #T
set TM=@tm,
@tm=case when CategoryID=@CategoryID then isnull(@tm+1,0) else 1 end,
@CategoryID=CategoryIDUpdate T
set Number=A.TM
From T, (select CategoryID,MemberID,min(Tm) as TM from #t group by CategoryID,MemberID) A
where T.CategoryID=A.CategoryID
and T.MemberID=A.MemberID
GOSelect * from T
/*9 1135 1
9 5512 2
9 83 3
9 61 4
9 52 5
9 61 4
11 123 1
11 89 2
11 11 3
11 76 4
14 32 1
14 989 2*/GO
Drop table #T
Drop Table T
drop table tb
go
create table tb(CategoryID int, MemberID int, Number int)
insert tb select 9,1135,null
insert tb select 9,5512,null
insert tb select 9,83,null
insert tb select 9,61,null
insert tb select 9,52,null
insert tb select 9,61,null
insert tb select 11,123,null
insert tb select 11,89,null
insert tb select 11,11,null
insert tb select 11,76,null
insert tb select 14,32,null
insert tb select 14,989,null
go
update a set a.number=b.number
from
tb a,
(
select CategoryID,MemberID,number= row_number() OVER (PARTITION BY CategoryID order by getdate())
from tb
) b
where a.CategoryID=b.CategoryID and a.MemberID=b.MemberIDselect * from tb
/*
CategoryID MemberID Number
----------- ----------- -----------
9 1135 1
9 5512 2
9 83 3
9 61 4
9 52 5
9 61 4
11 123 1
11 89 2
11 11 3
11 76 4
14 32 1
14 989 2(12 行受影响)
*/这下可以结了。