求最佳查询(SQL server)

[redidea]

有一表Ta有两个字段
ItemNo (varchar(15)) 和 Priority (int default 0)
关联表Tb有两个字段
ItemNo (varchar(15)) 和 ItemName (varchar(50))

Priority默认值是0，大约有n个是大于0的，数字越小，优先级越高 (例外：0的优先级最低)

求：
取m个记录，包含ItemNo,ItemName，要求：前面x个是按照优先级排序，后面m-x个随机取得

征最佳方案

本人的解决方案如下，用到游标，不爽：

CREATE procedure procGetItem
@MaxNum int, --总共数量
@FixNum int --固定数量
as
declare @vSql nvarchar(4000)
set @strMaxNum = cast(@MaxNum as varchar(5))
declare @splitPriority int
set @splitPriority=1
declare @i int
set @i=1

declare split_cursor CURSOR
For select priority from Ta where priority<>0 order by Priority asc
open split_cursor
fetch next from split_cursor into @splitPriority
WHILE @@FETCH_STATUS = 0 and @i<@FixNum
begin
set @i=@i+1
fetch next from split_cursor into @splitPriority
end
CLOSE split_cursor
DEALLOCATE split_cursor

set @vSql ='select top '+cast(@MaxNum as varchar(5))+' a.*,b.ItemName'
set @vSql = @vSql+ ' from '
set @vSql = @vSql+ ' (select ItemNo,Priority,''00000000-0000-0000-0000-000000000000'' as cPriority from Ta where Priority>0 and department='+@strDEPA+' and priority<='+cast(@SplitPriority as varchar(5))
set @vSql = @vSql+ ' union'
set @vSql = @vSql+ ' select ItemNo,1000000 as Priority,newid() as cPriority from Ta where Priority=0 or priority>'+cast(@SplitPriority as varchar(5))+')'
set @vSql = @vSql+ ' ) a'
set @vSql = @vSql+ ' left join Tb b on a.ItemNo=b.ItemNo'
set @vSql = @vSql+ ' order by a.priority,a.cPriority'
--print @vSql
exec sp_executesql @vSql
GO

[zjcxc]

select a.*,b.ItemName
from Ta a join Tb b on a.ItemNo=b.ItemNo

这个结果中,有没有可以做主键的?

[zjcxc]

--看楼主自己的做法,是用 ta 表的 Priority 做主键

--所以楼主的处理方法可以修改为:

CREATE procedure procGetItem
@MaxNum int, --总共数量
@FixNum int --固定数量
as
declare @vSql nvarchar(4000)
set @strMaxNum = cast(@MaxNum as varchar(5))
declare @splitPriority int
set @splitPriority=1
declare @i int
set @i=1

/*--改掉这部分
--因为楼主的存储过程有语法错误,所以不知道楼主的实际处理是怎么样的
--所以仅改掉游标处理的部分

declare split_cursor CURSOR
For select priority from Ta where priority<>0 order by Priority asc
open split_cursor
fetch next from split_cursor into @splitPriority
WHILE @@FETCH_STATUS = 0 and @i<@FixNum
begin
set @i=@i+1
fetch next from split_cursor into @splitPriority
end
CLOSE split_cursor
DEALLOCATE split_cursor
--*/

--上述处理修改如下
set rowcount @FixNum
select @splitPriority=priority
from Ta where priority<>0 order by Priority
set rowcount 0
--修改的部分结束

set @vSql ='select top '+cast(@MaxNum as varchar(5))+' a.*,b.ItemName'
set @vSql = @vSql+ ' from '
set @vSql = @vSql+ ' (select ItemNo,Priority,''00000000-0000-0000-0000-000000000000'' as cPriority from Ta where Priority>0 and department='+@strDEPA+' and priority<='+cast(@SplitPriority as varchar(5))
set @vSql = @vSql+ ' union'
set @vSql = @vSql+ ' select ItemNo,1000000 as Priority,newid() as cPriority from Ta where Priority=0 or priority>'+cast(@SplitPriority as varchar(5))+')'
set @vSql = @vSql+ ' ) a'
set @vSql = @vSql+ ' left join Tb b on a.ItemNo=b.ItemNo'
set @vSql = @vSql+ ' order by a.priority,a.cPriority'
--print @vSql
exec sp_executesql @vSql
GO

[zwz99999]

up

[redidea]

邹建接分，谢过