如何分配序号？请高手快来帮忙

Question

create table #TableA(
UserID varchar(50), --摊位商户
AID char(2), --摊位商户的经营类型
BID char(2), --商场ID
Num int null --商户在当前商场的摊位序号
)
insert into #TableA (UserID,AID,BID)
select '李','7A','40' union all
select '王','7A','40' union all
select '张','6A','40' union all
select '路','76','40' union all
select '萧','3A','40' union all
select '烤','3A','40' union all
select '妹','32','40' union all
select '东','7A','40' union all
select '套','7A','40' union all
select '啊','7A','41' union all
select '的','7A','41' union all
select '恶','6A','41' union all
select '人','76','42' union all
select '我','3A','42' union all
select '哦','3A','42' union all
select '批','32','42' union all
select '去','7A','42' union all
select '没','7A','42'--现在的需求是：把商场里的商户按照其经营类型从新分配摊位，原则是：首先先排在当前商场中经营类型最多的经营类型的商户，然后再排其次多的经营类型的商户；当同一种经营类型的商户排序时，按照姓名升序排列进行分配摊位号。得的结果应该是：UserID AID BID Num
李 7A 40 1
王 7A 40 2
东 7A 40 3
套 7A 40 4
萧 3A 40 5
烤 3A 40 6
妹 32 40 7
张 6A 40 8
路 76 40 9
啊 7A 41 1
的 7A 41 2
恶 6A 41 3
我 3A 42 1
哦 3A 42 2
去 7A 42 3
没 7A 42 4
批 32 42 5
人 76 42 6--我该如何在不使用游标的情况下能获得每个商户在其商场内的序号呢？就像我在上面想要得到的数据结果一样。

Answer 1

update a
set num=(select count(*) from #TableA b where BID=a.BID and ((select count(*) from #TableA where BID=a.BID and aID=b.aId)>(select count(*) from #TableA where BID=a.BID and aID=a.aId)
           or (select count(*) from #TableA where BID=a.BID and aID=b.aId)=(select count(*) from #TableA where BID=a.BID and aID=a.aId) and b.aid<a.aid
           or b.aid=a.aid and UserID<=a.UserID
   )
)
from #TableA aselect * from #TableA
order by bid,num--结果
UserID                                             AID  BID  Num         
-------------------------------------------------- ---- ---- ----------- 
李                                                  7A   40   1
王                                                  7A   40   2
东                                                  7A   40   3
套                                                  7A   40   4
萧                                                  3A   40   5
烤                                                  3A   40   6
妹                                                  32   40   7
张                                                  6A   40   8
路                                                  76   40   9
的                                                  7A   41   1
啊                                                  7A   41   2
恶                                                  6A   41   3
我                                                  3A   42   1
哦                                                  3A   42   2
没                                                  7A   42   3
去                                                  7A   42   4
批                                                  32   42   5
人                                                  76   42   6（所影响的行数为 18 行）

Answer 2

update a
set num=(select count(*) from #TableA b where BID=a.BID and ((select count(*) from #TableA where BID=a.BID and aID=b.aId)>(select count(*) from #TableA where BID=a.BID and aID=a.aId)
           or (select count(*) from #TableA where BID=a.BID and aID=b.aId)=(select count(*) from #TableA where BID=a.BID and aID=a.aId) and b.aid<a.aid
           or b.aid=a.aid and UserID<=a.UserID
   )
)
from #TableA aselect * from #TableA
order by bid,num--结果
UserID                                             AID  BID  Num         
-------------------------------------------------- ---- ---- ----------- 
李                                                  7A   40   1
王                                                  7A   40   2
东                                                  7A   40   3
套                                                  7A   40   4
萧                                                  3A   40   5
烤                                                  3A   40   6
妹                                                  32   40   7
张                                                  6A   40   8
路                                                  76   40   9
的                                                  7A   41   1
啊                                                  7A   41   2
恶                                                  6A   41   3
我                                                  3A   42   1
哦                                                  3A   42   2
没                                                  7A   42   3
去                                                  7A   42   4
批                                                  32   42   5
人                                                  76   42   6（所影响的行数为 18 行）

Answer 3

有一处排的不同
我的
没                                                  7A   42   3
去                                                  7A   42   4你的
去 7A 42 3
没 7A 42 4貌似我的排得对
没(mei)<去(qu)

Answer 4

没关系，Haiwer(海阔天空) 你的方法还是满足我的要求的

Answer 5

select count(*) from #TableA b where BID=a.BID and ((select count(*) from #TableA where BID=a.BID and aID=b.aId)>(select count(*) from #TableA where BID=a.BID and aID=a.aId)
           or (select count(*) from #TableA where BID=a.BID and aID=b.aId)=(select count(*) from #TableA where BID=a.BID and aID=a.aId) and b.aid<a.aid
           or b.aid=a.aid and UserID<=a.UserID
-----------------------------------------------------------
能给解释一下上边的语句吗？没想明白怎么就出了那个有序的序号？？

Answer 6

--为什么我的排序跟楼上的不一样呢？不是按拼音排序么？
create table #TableA(
UserID varchar(5), --摊位商户
AID char(2), --摊位商户的经营类型
BID char(2), --商场ID
Num int null --商户在当前商场的摊位序号
)
insert into #TableA (UserID,AID,BID)
select '李','7A','40' union all
select '王','7A','40' union all
select '张','6A','40' union all
select '路','76','40' union all
select '萧','3A','40' union all
select '烤','3A','40' union all
select '妹','32','40' union all
select '东','7A','40' union all
select '套','7A','40' union all
select '啊','7A','41' union all
select '的','7A','41' union all
select '恶','6A','41' union all
select '人','76','42' union all
select '我','3A','42' union all
select '哦','3A','42' union all
select '批','32','42' union all
select '去','7A','42' union all
select '没','7A','42'
select a.userid,a.aid,a.bid,count(b.aid) as num
into #tmp
from #tablea a
left join #tablea b on a.bid = b.bid and a.aid = b.aid
group by a.userid,a.aid,a.bid
order by num desc,a.bid,a.aidselect a.userid,a.aid,a.bid,count(b.userid)+1 as num
from #tmp a
left join #tmp b on a.bid = b.bid and (b.num > a.num or b.num = a.num and unicode(b.userid) < unicode(a.userid))
group by a.userid,a.aid,a.bid
order by a.bid,num
go
drop table #tablea,#tmp/*
userid aid  bid  num         
------ ---- ---- ----------- 
东      7A   40   1
套      7A   40   2
李      7A   40   3
王      7A   40   4
烤      3A   40   5
萧      3A   40   6
妹      32   40   7
张      6A   40   8
路      76   40   9
啊      7A   41   1
的      7A   41   2
恶      6A   41   3
去      7A   42   1
哦      3A   42   2
我      3A   42   3
没      7A   42   4
人      76   42   5
批      32   42   6(18 row(s) affected)
*/

Answer 7

不对，有个条件不用写成unicode(b.userid) < unicode(a.userid)，直接b.userid < a.userid就行了

Answer 8

mengmou()mengmou() 的算法有问题：
问题数据在：
去      7A   42   1
哦      3A   42   2
我      3A   42   3
没      7A   42   4
人      76   42   5
批      32   42   6
应该 “去      7A   42   1”和“没      7A   42   4” 连续的。
正确的数据是：
没 7A   42   1
去 7A   42   2
我 3A   42   3
哦 3A   42   4
批 32   42   5
人 76   42   6

Answer 9

to:mengmou()mengmou() 
没有把
去      7A   42   1  --排在一起
哦      3A   42   2
我      3A   42   3
没      7A   42   4  --排在一起

Answer 10

这下对了create table #TableA(
UserID varchar(5),--摊位商户
AID char(2),--摊位商户的经营类型
BID char(2),--商场ID
Num int null--商户在当前商场的摊位序号
)
insert into #TableA (UserID,AID,BID)
select '李','7A','40' union all
select '王','7A','40' union all
select '张','6A','40' union all
select '路','76','40' union all
select '萧','3A','40' union all
select '烤','3A','40' union all
select '妹','32','40' union all
select '东','7A','40' union all
select '套','7A','40' union all
select '啊','7A','41' union all
select '的','7A','41' union all
select '恶','6A','41' union all
select '人','76','42' union all
select '我','3A','42' union all
select '哦','3A','42' union all
select '批','32','42' union all
select '去','7A','42' union all
select '没','7A','42'
select a.userid,a.aid,a.bid,count(b.aid) as num
into #tmp
from #tablea a
left join #tablea b on a.bid = b.bid and a.aid = b.aid
group by a.userid,a.aid,a.bid
order by num desc,a.bid,a.aidselect a.userid,a.aid,a.bid,count(b.userid)+1 as num
from #tmp a
left join #tmp b 
on a.bid = b.bid 
and (
b.num > a.num 
or(
b.num = a.num 
and (a.aid = b.aid and b.userid < a.userid or a.aid > b.aid)
)
)
group by a.userid,a.aid,a.bid
order by a.bid,num
go
drop table #tablea,#tmp/*
userid aid  bid  num         
------ ---- ---- ----------- 
东      7A   40   1
李      7A   40   2
套      7A   40   3
王      7A   40   4
烤      3A   40   5
萧      3A   40   6
妹      32   40   7
张      6A   40   8
路      76   40   9
啊      7A   41   1
的      7A   41   2
恶      6A   41   3
哦      3A   42   1
我      3A   42   2
没      7A   42   3
去      7A   42   4
批      32   42   5
人      76   42   6
*/

Answer 11

friendlyFour(web开发四人组) ( ) 信誉：99  2007-7-17 16:58:18  得分: 0  
 
 
   
select count(*) from #TableA b where BID=a.BID and ((select count(*) from #TableA where BID=a.BID and aID=b.aId)>(select count(*) from #TableA where BID=a.BID and aID=a.aId)
           or (select count(*) from #TableA where BID=a.BID and aID=b.aId)=(select count(*) from #TableA where BID=a.BID and aID=a.aId) and b.aid<a.aid
           or b.aid=a.aid and UserID<=a.UserID
-----------------------------------------------------------
能给解释一下上边的语句吗？没想明白怎么就出了那个有序的序号？？-------------------------------------------------------------------
也许解释了比没解释更不好理解
  
select count(*)  --试图找出排在自己前面的记录个数,那就是序号了
from #TableA b 
where BID=a.BID  --BID必须相等,因为按BID分组排序
and ((select count(*) from #TableA where BID=a.BID and aID=b.aId) --这个子查询返回aID=b.aId的记录数
>(select count(*) from #TableA where BID=a.BID and aID=a.aId) --这个子查询返回aID=a.aId的记录数 ,中间>表示相同aID记录数多的排在前面
or      --如果相同aID记录数一样多
(select count(*) from #TableA where BID=a.BID and aID=b.aId)=(select count(*) from #TableA where BID=a.BID and aID=a.aId)  --如果相同aID记录数一样多
and b.aid<a.aid     --aid小的排在前面
           or       --如果都相同
b.aid=a.aid and UserID<=a.UserID      --UserID小的排在前面
 

Answer 12

Haiwer(海阔天空) 为什么是 李 王 东 套
而不是   东 李 套 王不是按拼音排序么？

Answer 13

对不起,以上诸位,你们的答案我都没有采用,原因是:速度太慢.
Haiwer(海阔天空) 给出的答案对 14786条记录操作时间是190秒
mengmou()mengmou()  给出的答案对 14786条记录操作时间是72秒
而最终我采用了只读前进游标的速度是：9秒。
所以我放弃你们的写法。因为最终用户要的是速度。抱歉
不过还是要谢谢两位给出了不同的解决方法。谢谢。以后还要不断的请教你们。