[SQL]交叉表的处理

  *

功能：交叉表求解
程序员：cosio
日期：2006-09-22
数据示例：
[id] [progid] [rights]
1 3302 1111
2 3302 1100
1 3303 1011
2 3303 1111
3 3303 1101

*/
--创建表
create table t
(
[id] int,
progid int,
rights int

)
--插入数据
insert into T
select 1,3302,1111 union all
select 2,3302,1100 union all
select 1,3303,1011 union all
select 2,3303,1111 union all
select 3,3303,1101

--创建存储过程
create proc sp_calc
(
@iLength int
)
AS
Begin
declare @iLen int
declare @iCount int
declare @sql nvarchar(1000)
declare @iTemp int
set @ilen=len(@ilength)
select @iCount=count(*) from t
if @iCount/2=0
begin
set @sql='select * from T where [id] in(select top '+ Rtrim(convert(char(10),@ilen))+' ' +'[id] from t)'
exec(@sql)
end
else
set @iTemp=(@iCount+1)/2
if @ilen<@iTemp
begin
set @sql='select * from T where [id] in(select top '+ Rtrim(convert(char(10),@ilen)) +' ' +'[id] from t)'
exec(@sql)
end
else
begin
set @sql='select * from T where [id] in(select top '+ Rtrim(convert(char(10),@ilen))+' ' +'[id] from t)'
set @sql=@sql+' '+'union select * from T where [id] not in(select top '+ Rtrim(convert(char(10),@ilen))+' '+'[id] from t) order by progid'
exec(@sql)
end
end


--结果示例:
exec sp_calc 1
1 3302 1111
1 3303 1011

exec sp_calc 12
1 3302 1111
2 3302 1100
1 3303 1011
2 3303 1111

exec sp_calc 123

1 3302 1111
2 3302 1100
1 3303 1011
2 3303 1111
3 3303 1101