求助动态参数组合统计表
数据表 T1
--------------
Id f1(人员) f2 (分数)
1 a 12
2 b 11
3 c 10
4 d 2
5 e 4
6 f 13
7 g 1
传入参数组合(格式)要求
a,b| c,d |e,f,g
第一组|第二组|第三组
要得到结果
-------------------
人 员 总得分 平均分
第一组 a, b 23 11.5
第二组 c, d 12 6
第一组 e,f,g 18 6
[解决办法]
写个函数，拆分存入的参数
[解决办法]
用存储过程实现，借助拆分函数。
/*
功能：实现split功能的函数
*/
create function fn_split
(
@inputstr varchar(8000),
@seprator varchar(10)
)
returns @temp table (a varchar(200))
as
begin
declare @i int
set @inputstr = rtrim(ltrim(@inputstr))
set @i = charindex(@seprator, @inputstr)
while @i > = 1
begin
insert @temp values(left(@inputstr, @i - 1))
set @inputstr = substring(@inputstr, @i + 1, len(@inputstr) - @i)
set @i = charindex(@seprator, @inputstr)
end
if @inputstr <> '\ '
insert @temp values(@inputstr)
return
end
go
[解决办法]
我写的是一个存储过程，不过没扩展到4个的组合，有必要楼主可以用这个方法自己扩展
create proc spgro
@pa nvarchar(20)= 'a,b|c,d|e,f,g ' --默认
as
declare @pa_n int,@str nvarchar(50)
select @pa=replace(@pa, ', ', ' ') --第一次初始化
select @str=@pa --定义可变字符串变量
select @pa_n=len(@pa)-len(replace(@pa, '| ', ' ')) --循环参数
declare @temptb table(id int identity(1,1),s_pa nvarchar(5)) --自定义数组
while (@pa_n> =0) begin --循环体
if charindex( '| ',@str) <> 0 insert into @temptb(s_pa) select left(@str,charindex( '| ',@str)-1)
else insert into @temptb(s_pa) select @str
select @str=right(@str,len(@str)-charindex( '| ',@str))
select @pa_n = @pa_n - 1
end --select * from @temptb
select a.*,b.f2+c.f2 总得分,cast((b.f2+c.f2) as float)/2 平均分 from @temptb a
inner join t1 b on b.f1=left(a.s_pa,1)
inner join t1 c on c.f1=right(a.s_pa,1)
where len(a.s_pa)=2
union all select a.*,b.f2+c.f2+d.f2 总得分,cast((b.f2+c.f2+d.f2) as float)/3 平均分 from @temptb a
inner join t1 b on b.f1=left(a.s_pa,1)
inner join t1 c on c.f1=right(a.s_pa,1)
inner join t1 d on d.f1=substring(a.s_pa,2,1)
where len(a.s_pa)=3 --还可以继续扩展
exec spgro 'a,b|c,d,e|b,d,g|a,f '
显示
1ab2311.5
4af2512.5
3bdg144.666666666666667
2cde165.333333333333333
有问题大家继续交流啊~~谢谢先~~~~~
[解决办法]
create table tb(id varchar(10) , code varchar(10))
create table T1(id int , f1 varchar(10), f2 decimal(18,2))
insert into t1(id,f1,f2) values(1, 'a ', 12)
insert into t1(id,f1,f2) values(2, 'b ', 11)
insert into t1(id,f1,f2) values(3, 'c ', 10)
insert into t1(id,f1,f2) values(4, 'd ' , 2)
insert into t1(id,f1,f2) values(5, 'e ', 4)
insert into t1(id,f1,f2) values(6, 'f ', 13)
insert into t1(id,f1,f2) values(7 , 'g ', 1)
go
declare @str varchar(4000)
declare @tmpstr varchar(10)
set @str= 'a,b|c,d|e,f,g ' + '| '
set @tmpstr= ' '
declare @i int
set @i=0
declare @j int
set @j=0
while @i <len(@str)
begin
set @i=@i+1
if SUBSTRING(@str,@i,1)= '| '
begin
set @j = @j + 1
insert tb(id , code) values( '第 ' + cast(@j as varchar) + '组 ' , left(@str,@i-1))
set @str=SUBSTRING(@str,@i+1,len(@str))
set @i=0
end
end
select t.id as 组别, t.code as 人员 , sum(t.f2) as 总得分 , cast(avg(t.f2) as decimal(18,2)) as 平均分 from
(
select t1.f1,t1.f2,tb.* from t1 , tb where charindex(t1.f1, tb.code) > 0
) t
group by id , code
drop table tb,t1
/*
组别 人员 总得分 平均分
---------- ---------- ------- --------------------
第1组 a,b 23.00 11.50
第2组 c,d 12.00 6.00
第3组 e,f,g 18.00 6.00
（所影响的行数为 3 行）
*/
