select top 100 with ties UserID, AreaCode, from NNIS_DBQB_STATUS 你帮我改改
CREATE procedure AP_DBQB_GET_TOP_SCORE @SType char(1) as begin if @SType = 'd' begin --前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同 select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfDay desc end else if @SType = 'w' begin select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfWeek desc end else if @SType = 'm' begin select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfMonth desc end else if @SType = 't' begin select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfTotal desc end end GO
CREATE procedure AP_DBQB_GET_TOP_SCORE @SType char(1) as begin if @SType = 'd' begin --前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同 select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfDay desc end else if @SType = 'w' begin select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfWeek desc end else if @SType = 'm' begin select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfMonth desc end else if @SType = 't' begin select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfTotal desc end end GO
可能中文的问题:select top 100 with ties isnull((select count(*) from NNIS_DBQB_STATUS where ScoreOfDay>a.ScoreOfDay),0)+1 as iCoun,UserID, AreaCode, from NNIS_DBQB_STATUS order by ScoreOfDay desc
select top 100 with ties isnull((select count(*) from NNIS_DBQB_STATUS where ScoreOfDay>a.ScoreOfDay),0)+1 as iCoun,UserID, AreaCode,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfDay desc
在Ms Sql Server 2000中用这个是没有问题的。CREATE procedure AP_DBQB_GET_TOP_SCORE @SType char(1) as begin select UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime into #t1 from NNIS_DBQB_STATUS where 1=2 if @SType = 'd' begin --前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同 insert into #t1 select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfDay desc end else if @SType = 'w' begin insert into #t1 select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfWeek desc end else if @SType = 'm' begin insert into #t1 select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfMonth desc end else if @SType = 't' begin insert into #t1 select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfTotal desc end select IDENTITY(int,0,1) as [order],* into #t2 from #t1 select * from #t2 drop table #t1 drop table #t2 end GO
--没有仔细看,原来每个条件选出来的字段是不一样的,上述有错,现已改正。CREATE procedure AP_DBQB_GET_TOP_SCORE @SType char(1) as begin if @SType = 'd' begin --前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同 select IDENTITY(int,0,1) as [order],UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime into #d from NNIS_DBQB_STATUS where 1=2 insert #d(UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime) select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfDay desc select * from #d drop table #d end else if @SType = 'w' begin select IDENTITY(int,0,1) as [order],UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime into #w from NNIS_DBQB_STATUS where 1=2 insert #w(UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime) select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfWeek desc select * from #w drop table #w end else if @SType = 'm' begin select IDENTITY(int,0,1) as [order],UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime into #m from NNIS_DBQB_STATUS where 1=2 insert #m(UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime) select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfMonth desc select * from #m drop table #m end else if @SType = 't' begin select IDENTITY(int,0,1) as [order],UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime into #t from NNIS_DBQB_STATUS where 1=2 insert #t(UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime) select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime from NNIS_DBQB_STATUS order by ScoreOfTotal desc select * from #t drop table #t end end GO
结果是
userID areacode
20 上海
30 北京
49 深圳
我要的结果是
order userID areacode
1 20 上海
2 30 北京
3 49 深圳
UserId<a.UserId
该为:
UserId<=a.UserId
不过你要求的是identity(0,1),从0开始的哦!
from NNIS_DBQB_STATUS
你帮我改改
@SType char(1)
as
begin
if @SType = 'd'
begin
--前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同
select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfDay desc
end
else
if @SType = 'w'
begin
select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfWeek desc
end
else
if @SType = 'm'
begin
select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfMonth desc
end
else
if @SType = 't'
begin
select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfTotal desc
end
end
GO
@SType char(1)
as
begin
if @SType = 'd'
begin
--前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同
select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfDay desc
end
else
if @SType = 'w'
begin
select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfWeek desc
end
else
if @SType = 'm'
begin
select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfMonth desc
end
else
if @SType = 't'
begin
select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfTotal desc
end
end
GO
from NNIS_DBQB_STATUS order by ScoreOfDay desc
from NNIS_DBQB_STATUS order by ScoreOfDay desc
@SType char(1)
as
begin
select UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime
into #t1 from NNIS_DBQB_STATUS where 1=2 if @SType = 'd'
begin
--前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同
insert into #t1
select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfDay desc
end
else
if @SType = 'w'
begin
insert into #t1
select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfWeek desc
end
else
if @SType = 'm'
begin
insert into #t1
select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfMonth desc
end
else
if @SType = 't'
begin
insert into #t1
select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfTotal desc
end
select IDENTITY(int,0,1) as [order],* into #t2 from #t1
select * from #t2
drop table #t1
drop table #t2
end
GO
他的问题是按ScoreOf...排序,有同分的情况,同分应该同名次。(个人理解,楼主没说,呵呵)
@SType char(1)
as
begin
if @SType = 'd'
begin
--前@Top名可能存在有些用户和最后一名同名次,但没有选上的情况,应该加上这些用户,用with ties语句可以达到这个效果,下同
select IDENTITY(int,0,1) as [order],UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime
into #d from NNIS_DBQB_STATUS where 1=2
insert #d(UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime)
select top 100 with ties UserID,ScoreOfDay, AnsNumOfDay, CorNumOfDay, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfDay desc
select * from #d
drop table #d
end
else
if @SType = 'w'
begin
select IDENTITY(int,0,1) as [order],UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime
into #w from NNIS_DBQB_STATUS where 1=2
insert #w(UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime)
select top 100 with ties UserID, ScoreOfWeek,AreaCode, AnsNumOfWeek, CorNumOfWeek,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfWeek desc
select * from #w
drop table #w
end
else
if @SType = 'm'
begin
select IDENTITY(int,0,1) as [order],UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime
into #m from NNIS_DBQB_STATUS where 1=2
insert #m(UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime)
select top 100 with ties UserID, ScoreOfMonth, AnsNumOfMonth, CorNumOfMonth,StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfMonth desc
select * from #m
drop table #m
end
else
if @SType = 't'
begin
select IDENTITY(int,0,1) as [order],UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime
into #t from NNIS_DBQB_STATUS where 1=2
insert #t(UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime)
select top 100 with ties UserID,ScoreOfTotal, AnsNumOfTotal, CorNumOfTotal, StartTime, LatestTime
from NNIS_DBQB_STATUS order by ScoreOfTotal desc
select * from #t
drop table #t
end
end
GO