老问题，还是求一统计SQL语句，解决马上给分...
求SQL语句，解决马上给分:
表：v_PtStore
F_ID F_Specs F_StoreNumber
1 1.0*2.0*6000 1
2 1.0*2.0*6000 2
3 1.15*2.25*6000 5
4 1.2*3.2*6000 1
5 20*30*1.2*6000 1
5 20*50*1.2*6000 1
要求统计结果：
1.0 1.15 1.2
2.0 3
2.25 5
3.2 1
20*30 1
20*50 1
大概有几千种F_Specs。谢谢
[解决办法]
create table xyz (F_ID int, F_Specs varchar(100), F_StoreNumber int)
insert xyz select 1 , '1.0*2.0*6000 ', 1
union all select 2 , '1.0*2.0*6000 ', 2
union all select 3 , '1.15*2.25*6000 ', 5
union all select 4 , '1.2*3.2*6000 ', 1
select f_id,a=left(f_specs,charindex( '* ',f_specs)-1),
b=substring(f_specs,charindex( '* ',f_specs)+1, charindex( '* ',f_specs,charindex( '* ',f_specs)+1)-charindex( '* ',f_specs)-1),
c=right(f_specs,len(f_specs)-charindex( '* ',f_specs,charindex( '* ',f_specs)+1)),
f_storenumber into # from xyz
declare @a varchar(1000)
select @a=isnull(@a+ ', ', ' ')+ ' sum(case when b= '+ltrim(b)+ ' then f_storenumber else 0 end) [ ' +ltrim(b)+ '] ' from # group by b
exec( 'select a, '+@a + ' from # group by a ')
drop table #
[解决办法]
create table v_PtStore (F_ID int,F_Specs varchar(20),F_StoreNumber int)
insert into v_PtStore values(1, '1.0*2.0*6000 ' , 1)
insert into v_PtStore values(2 , '1.0*2.0*6000 ' , 2)
insert into v_PtStore values(3 , '1.15*2.25*6000 ', 5)
insert into v_PtStore values(4 , '1.2*3.2*6000 ' , 1)
select cast(substring(f_specs , 1 , charindex( '* ' , f_specs) - 1) as varchar) f_specs1, cast(substring(f_specs , charindex( '* ' , f_specs) + 1 , charindex( '* ',f_specs,charindex( '* ' , f_specs) + 1) - charindex( '* ' , f_specs) - 1) as varchar) f_specs2, F_StoreNumber into #temp from v_ptstore
go
declare @sql varchar(8000)
set @sql = 'select f_specs1 '
select @sql = @sql + ' , sum(case f_specs2 when ' ' ' + f_specs2 + ' ' ' then F_StoreNumber else 0 end) [ ' + f_specs2 + '] '
from (select distinct f_specs2 from #temp) as a
set @sql = @sql + ' from #temp group by f_specs1 '
exec(@sql)
drop table v_PtStore,#temp
/*
f_specs1 2.0 2.25 3.2
------------------------------ ----------- ----------- -----------
1.0 3 0 0
1.15 0 5 0
1.2 0 0 1
*/
[解决办法]
create table v_PtStore(F_ID int , F_Specs nvarchar(50), F_StoreNumber int )
insert into v_PtStore
select 1, '1.0*2.0*6000 ' , 1
union select 2, '1.0*2.0*6000 ' , 2
union select 3, '1.15*2.25*6000 ', 5
union select 4, '1.2*3.2*6000 ' , 1
union select 5, '20*30*1.2*6000 ', 1
union select 5, '20*50*1.2*6000 ', 1
go
create function num_h(@F_Specs nvarchar(50))
returns nvarchar(20)
as
begin
declare @aa nvarchar(50)
declare @return nvarchar(20)
if len(@F_Specs)- len(replace(@F_Specs, '* ', ' '))=2
begin
select @aa=substring(@F_Specs,charindex( '* ',@F_Specs)+1,len(@F_Specs))
select @return=substring(@aa,1,charindex( '* ',@aa)-1)
end
else
begin
select @aa=substring(@F_Specs,charindex( '* ',@F_Specs)+1,len(@F_Specs))
select @aa=substring(@aa,charindex( '* ',@aa)+1,len(@aa))
select @return=substring(@aa,1,charindex( '* ',@aa)-1)
end
return @return
end
create function num_l(@F_Specs nvarchar(50))
returns nvarchar(20)
as
begin
declare @aa nvarchar(50)
declare @return nvarchar(20)
if len(@F_Specs)- len(replace(@F_Specs, '* ', ' '))=2
begin
select @return=substring(@F_Specs,1,charindex( '* ',@F_Specs)-1)
end
else
begin
select @return=substring(@F_Specs,1,charindex( '* ',@F_Specs))
select @aa=substring(@F_Specs,charindex( '* ',@F_Specs)+1,len(@F_Specs))
select @return=@return+substring(@aa,1,charindex( '* ',@aa)-1)
end
return @return
end
declare @sql nvarchar(4000)
set @sql= ' '
select @sql=@sql+ 'sum(case dbo.num_h(F_Specs) when ' ' '+ aa + ' ' ' then F_StoreNumber end) as ' ' '+aa+ ' ' ', '
from (select distinct dbo.num_h(F_Specs) as aa from v_PtStore)a
select @sql= 'select dbo.num_l(F_Specs) as a , ' +left(@sql,len(@sql)-1 )+ 'from v_PtStore group by dbo.num_l(F_Specs) '
print @sql
exec( @sql)
