j9988或其它大侠请进来帮忙，存储过程优化问题

Question

读取userinfo中score中的数据，然后又用了一个循环去计算获得的奖品。

Answer 1

谢谢 leisa(飞翔要远行) 
但积分相同的客户可能不多

Answer 2

呵呵！终于搞定了！！！
原理：我想大家应该能看懂select b.username,a.prizename,
case a.prizename 
  when '手表' then b.score/200
  when '打火机' then b.score%200/50
end prizecount,a.*,b.* from prizeinfo a,userinfo b order by username根据上面的原理就可以构造出思路了！！！

Answer 3

下面是代码，我没吃饭搞了我半小时！希望楼主不要食言！！！
我还特地翻译了一下！
declare @奖品分数 int,@奖品名 varchar(20),@内部游标变量A int,@内部游标变量B varchar(100)
declare @SQL语句 varchar(4000)
set @SQL语句='select b.username,a.prizename,case a.prizename'
declare 奖品游标 CURSOR FOR select prizescore,prizename from prizeinfo
OPEN 奖品游标
FETCH NEXT FROM 奖品游标 into @奖品分数,@奖品名
WHILE @@FETCH_STATUS = 0
BEGIN
  set @SQL语句=@SQL语句+' when '''+@奖品名+''' then b.score'
  set @内部游标变量B=''  declare 内部游标 cursor for select prizescore from prizeinfo where prizescore>=@奖品分数 order by prizescore
  open 内部游标
  fetch from 内部游标 into @内部游标变量A
  while @@FETCH_STATUS = 0
  begin
    if @内部游标变量B=''
      set @内部游标变量B='/'+cast(@内部游标变量A as varchar(10))
    else
      set @内部游标变量B='%'+cast(@内部游标变量A as varchar(10))+@内部游标变量B
    fetch from 内部游标 into @内部游标变量A
  end
  close 内部游标
  DEALLOCATE 内部游标  set @SQL语句=@SQL语句+@内部游标变量B
  FETCH NEXT FROM 奖品游标 into @奖品分数,@奖品名
ENDCLOSE 奖品游标
DEALLOCATE 奖品游标
select @SQL语句
set @SQL语句=@SQL语句+' end from prizeinfo a,userinfo b order by b.username'exec(@SQL语句)

Answer 4

前面的嵌套游标只是为了构造出
“select b.username,a.prizename,
case a.prizename 
  when 'aa' then b.score/200
  when 'bb' then b.score%200/50
  when 'cc' then b.score%200%50/20
end prizecount,a.*,b.* from prizeinfo a,userinfo b order by username”
代码，真正执行是“exec(@SQL语句)”
我想速度一定快！！！

Answer 5

不知道你要不要减去原表userinfo中的score 所以用个临时表#t
如果要减userinfo中的score，就不用#t,并把下面的#t换成userinfo　。select * into #t from userinfowhile exists
 (
 select 1 
 from #t a,prizeinfo b 
 where b.prizescore<=a.score and b.prizescore in 
   (select max(prizescore) 
    from prizeinfo 
    where prizescore<=a.score)
)
beginselect distinct 
  a.username,
  b.prizename,
  b.prizescore,
  a.score/b.prizescore prizecount into #t2 
from #t a,prizeinfo b 
where b.prizescore<=a.score and b.prizescore in 
   (select max(prizescore) 
    from prizeinfo 
    where prizescore<=a.score)update a set score=score-b.prizescore*b.prizecount 
from #t a,#t2 b 
where a.username=b.username insert sentprize select username,prizename,prizecount from #t2drop table #t2end
select * from sentprize order by usernamedrop table #t--delete from sentprize

Answer 6

如果并发性不高，建议用临时表：
建一个临时表#a，人员，奖品,积分
做一个循环，
把可扣数最大积分数的奖品人员放入临时表
（insert into #aa 
select 人员，（select max(积分)　where 人员表的积分>奖品的积分），积分　from 人员表）
扣一次分数
（update set =（select max(积分)　where 人员表的积分>奖品的积分），积分　from 人员表）
不能再扣的删除
直到人员表没有记录为止结束循环　
然后利用这个临时表按奖品分组就可得到结果
比如所举例所得的临时表里记录为
张三        手表             200
张三        打火机           50
张三        打火机           50
张三        打火机           50
李四        手表             200     
李四        手表             200
李四        手表             200
李四        手表             200
李四        手表             200
不知我有没有表述明白。（没写代码，按理应该可以）

Answer 7

不知道你要不要减去原表userinfo中的score 所以用个临时表#t
如果要减userinfo中的score，就不用#t,并把下面的#t换成userinfo　。select * into #t from userinfowhile exists
 (
 select 1 
 from #t a,prizeinfo b 
 where b.prizescore<=a.score and b.prizescore in 
   (select max(prizescore) 
    from prizeinfo 
    where prizescore<=a.score)
)
beginselect distinct 　　--此处distinct防止有相同分的奖品，没有可不要
  a.username,
  b.prizename,
  b.prizescore,
  a.score/b.prizescore prizecount into #t2 
from #t a,prizeinfo b 
where b.prizescore<=a.score and b.prizescore in 
   (select max(prizescore) 
    from prizeinfo 
    where prizescore<=a.score)update a set score=score-b.prizescore*b.prizecount 
from #t a,#t2 b 
where a.username=b.username insert sentprize select username,prizename,prizecount from #t2drop table #t2enddrop table #t--select * from sentprize order by username
--delete from sentprize

Answer 8

先更正一下：
declare @奖品分数 int,@奖品名 varchar(20),@内部游标变量A int,@内部游标变量B varchar(100)
declare @SQL语句 varchar(4000)
set @SQL语句='select b.username,a.prizename,case a.prizename'
declare 奖品游标 CURSOR FOR select prizescore,prizename from prizeinfo
OPEN 奖品游标
FETCH NEXT FROM 奖品游标 into @奖品分数,@奖品名
WHILE @@FETCH_STATUS = 0
BEGIN
  set @SQL语句=@SQL语句+' when '''+@奖品名+''' then b.score'
  set @内部游标变量B=''  declare 内部游标 cursor for select prizescore from prizeinfo where prizescore>=@奖品分数 order by prizescore
  open 内部游标
  fetch from 内部游标 into @内部游标变量A
  while @@FETCH_STATUS = 0
  begin
    if @内部游标变量B=''
      set @内部游标变量B='/'+cast(@内部游标变量A as varchar(10))
    else
      set @内部游标变量B='%'+cast(@内部游标变量A as varchar(10))+@内部游标变量B
    fetch from 内部游标 into @内部游标变量A
  end
  close 内部游标
  DEALLOCATE 内部游标  set @SQL语句=@SQL语句+@内部游标变量B
  FETCH NEXT FROM 奖品游标 into @奖品分数,@奖品名
ENDCLOSE 奖品游标
DEALLOCATE 奖品游标
select @SQL语句
set @SQL语句=@SQL语句+' end prizecount into #AA from prizeinfo a,userinfo b order by b.username;select * from #AA where prizecount<>0'exec(@SQL语句)

Answer 9

to:zqllyh(您问我也问总可以问出个所以然) 大虾
  你试试我的执行效率怎样呢？？？？速度还行吧！

Answer 10

我想100000条语句的话！用
“select b.username,a.prizename,
case a.prizename 
  when 'aa' then b.score/200
  when 'bb' then b.score%200/50
  when 'cc' then b.score%200%50/20
end prizecount,a.*,b.* from prizeinfo a,userinfo b order by username”
会比子查询要快！

Answer 11

如果论效率来说，我认为我的思路循环不会超过获得某奖品的最大数目，相信不会过百，速度肯定快一点。
pengdali(大力) 的办法是可行，但效率应该不行。
j9988(j9988) 的办法好象没有解出问题来

Answer 12

谢谢大家。
to zqllyh(您问我也问总可以问出个所以然),pengdali(大力)和j9988(j9988) ( )三位的方法我都看了，代码没有详细看，但你们的意思我明白了，pengdali(大力)的方法也许效率最高（没有试，看起来是这样），待会我会一个个的试一下，有兴趣可以继续关注这个帖子。
共900分已送出（好心痛啊），还有两百分，等到我试试谁的方法效率最高再送出。
http://expert.csdn.net/Expert/topic/1202/1202622.xml?temp=.6960718
http://expert.csdn.net/Expert/topic/1202/1202583.xml?temp=.4684564
http://expert.csdn.net/Expert/topic/1202/1202589.xml?temp=.7869379
http://expert.csdn.net/Expert/topic/1202/1202595.xml?temp=.4271356
http://expert.csdn.net/Expert/topic/1202/1202600.xml?temp=.2858393
http://expert.csdn.net/Expert/topic/1202/1202602.xml?temp=.3497431
http://expert.csdn.net/Expert/topic/1202/1202607.xml?temp=.7371942
http://expert.csdn.net/Expert/topic/1202/1202611.xml?temp=.371731
http://expert.csdn.net/Expert/topic/1202/1202624.xml?temp=.2638208

Answer 13

to  dododo(心奕) 谢谢！！！谢谢！！！
:)to zqllyh(您问我也问总可以问出个所以然) 大虾
  不知大虾试过没有，j9988(j9988) 的办法可以解出来但效率很底（恕我直言）呵呵！！我的方法你试试便知速度如何！！！呵呵！！

Answer 14

补充：同意zqllyh(您问我也问总可以问出个所以然) 大虾的思路!!!

Answer 15

好久沒來sql版逛啦。:)
試試我這個:
---SQL 2K下測試OK.DECLARE @prezeInfo TABLE (prezeID NVARCHAR(20), prezeScore INT )
DECLARE @userInfo TABLE (userID NVARCHAR(10),score INT)
DECLARE @sentpreize TABLE(userID NVARCHAR(10),prezeID NVARCHAR(20),prizecount INT)
insert into @prezeInfo values ('手表',200)
insert into @prezeInfo values ('打火機',50)
insert into @userInfo values ('張三',350)
insert into @userInfo values ('李四',1000)
DECLARE @UserID NVARCHAR(10),
      @Score INT,
      @prezecount int
SET @UserID=''
SET @Score=0
select *,cast(null as int) as prezecount into #tmp from @userinfo,@prezeinfo order by userID,prezescore desc
 
update #tmp
set  @score =case when @userid<>userid then score%prezescore else @score end,
@prezecount=case when @userID<>userid then score/prezescore else @score/prezescore end,
@userid=case when @userid<>userid then userid else @userid end,
prezecount=@prezecountselect * from #tmp
drop table #tmp

Answer 16

然後根據#tmp表的內容insert into到sentprize表中，把prezecount=0的記錄剔掉。

Answer 17

to N_chow(mysqlsky.126.com) 前辈好！
速度绝对OK!呵呵！但出错：(我想改改就是最好的解决方案！)
DECLARE @prezeInfo TABLE (prezeID NVARCHAR(20), prezeScore INT )
DECLARE @userInfo TABLE (userID NVARCHAR(10),score INT)
DECLARE @sentpreize TABLE(userID NVARCHAR(10),prezeID NVARCHAR(20),prizecount INT)
insert into @prezeInfo values ('手表',200)
insert into @prezeInfo values ('打火機',50)
insert into @prezeInfo values ('打aa',20)
insert into @userInfo values ('張三',350)
insert into @userInfo values ('李四',1000)
insert into @userInfo values ('mm',2000)
DECLARE @UserID NVARCHAR(10),
      @Score INT,
      @prezecount int
SET @UserID=''
SET @Score=0
select *,cast(null as int) as prezecount into #tmp from @userinfo,@prezeinfo order by userID,prezescore desc
 
update #tmp
set  @score =case when @userid<>userid then score%prezescore else @score end,
@prezecount=case when @userID<>userid then score/prezescore else @score/prezescore end,
@userid=case when @userid<>userid then userid else @userid end,
prezecount=@prezecountselect * from #tmp
drop table #tmp

Answer 18

若是樓主的規則就這他所說的:产生的规则就是：根据用户的积分发放最大的奖品。那麼我結果應該沒錯，我仔細比對了一下，
select  * from @prezeinfo  --preze info
select * from @userinfo    --user info--sentprize info
select  userid,prezeid,prezescore,prezecount 
from    #tmp 
where   prezecount<>0

Answer 19

to N_chow(mysqlsky.126.com) 大侠
试试我上面贴的代码，我只在你的基础上加了两条数据
张三的结果错了

Answer 20

:-)
再次修正。DECLARE @prezeInfo TABLE (prezeID NVARCHAR(20), prezeScore INT )
DECLARE @userInfo TABLE (userID NVARCHAR(10),score INT)
DECLARE @sentpreize TABLE(userID NVARCHAR(10),prezeID NVARCHAR(20),prizecount INT)
insert into @prezeInfo values ('手表',200)
insert into @prezeInfo values ('打火機',50)
insert into @prezeInfo values ('打aa',30)
insert into @prezeInfo values ('sdf打aa',10)
insert into @userInfo values ('張三',350)
insert into @userInfo values ('李四',1000)
insert into @userInfo values ('mm',2000)
DECLARE @UserID NVARCHAR(10),
      @Score INT,
      @prezecount int,
     @tmpScore INT
SET @UserID=''
SET @Score=0
select *,cast(null as int) as prezecount into #tmp from @userinfo,@prezeinfo order by userID,prezescore desc
 
update #tmp
set  @tmpScore=@score,
@score =case when @userid<>userid then score%prezescore else @score%prezescore end,
@prezecount=case when @userID<>userid then score/prezescore else @tmpscore/prezescore end,
@userid=case when @userid<>userid then userid else @userid end,
prezecount=@prezecount
select  UserID,Score,PrezeID,PrezeScore,Prezecount  from #tmp  where prezecount<>0
drop table #tmp

Answer 21

to:： N_chow(一劍飄香)
先在这里谢过了，
下次在sql server论坛提问，我会请这样问
'N_chow(一劍飄香),zqllyh(您问我也问总可以问出个所以然),pengdali(大力)和j9988(j9988) 或其它大侠请进来帮忙.....'。^-^另：有几个问题，还要请教（帮人帮到底吧，哈哈。），
1、
DECLARE @prezeInfo TABLE (prezeID NVARCHAR(20), prezeScore INT )
DECLARE @userInfo TABLE (userID NVARCHAR(10),score INT)
DECLARE @sentpreize TABLE(userID NVARCHAR(10),prezeID NVARCHAR(20),prizecount 
这三句是定义临时表吗？
2、
update #tmp
set  @tmpScore=@score,
@score =case when @userid<>userid then score%prezescore else @score%prezescore end,
@prezecount=case when @userID<>userid then score/prezescore else @tmpscore/prezescore end,
@userid=case when @userid<>userid then userid else @userid end,
prezecount=@prezecount
我可不可以理解为：在更新数据集时会每更新一条记录就会执行一遍以上的付值

Answer 22

我不常來(以前常來，呵呵),這種方法j9988老哥以前用過，所以我才這麼說，千萬別誤會。
1.是的，是定議table變量。做測試用的。
2.可以這麼理解.
3.用temp table轉是為了讓update語句執行正確。

Answer 23

我想 N_chow(一劍飄香) 的方法是最好的方法了，
N_chow(一劍飄香) 请接分。问题解决了真好，还学到了不少东西，以后要多散分一些。