如何分配序号？请高手快来帮忙
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 '
--现在的需求是：把商场里的商户按照其经营类型从新分配摊位，原则是：首先先排在当前商场中经营类型最多的经营类型的商户，然后再排其次多的经营类型的商户；当同一种经营类型的商户排序时，按照姓名升序排列进行分配摊位号。得的结果应该是：
UserIDAIDBIDNum
李7A401
王7A402
东7A403
套7A404
萧3A405
烤3A406
妹32407
张6A408
路76409
啊7A411
的7A412
恶6A413
我3A421
哦3A422
去7A423
没7A424
批32425
人76426
--我该如何在不使用游标的情况下能获得每个商户在其商场内的序号呢？就像我在上面想要得到的数据结果一样。
[解决办法]
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 a
select * 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 行）
[解决办法]
这下对了
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.aid
select 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
*/
