100分求使用SQL实现一个比较复杂的统计问题,请来看看...
产品ID 规格 数量
1 1.0*2.0*6000 1
2 1.0*2.0*6000 1
3 1.1*2.0*6000 1
4 1.2*3.2*6000 1
统计结果：
1.0 1.1 1.2
2.0 2 1
3.2 1
[解决办法]
ALTER PROCEDURE dbo.stat
AS
SET NOCOUNT ON
select 产品ID,left(规格,3) as L,right(left(规格,7),3) as W, right(规格,4) as H ,数量 into #T from [product]
declare @sql varchar(1000)
select L,Sum(total) as LC into #t1 from #t group by L
set @sql = 'select W '
select @sql = @sql + ',sum(case when L= ' ' ' + L + ' ' ' then total else 0 end) as ' ' ' + L + ' ' ' ' from #t1
set @sql = @sql + ' from #t Group by w '
execute(@sql)
RETURN
*****************************************************************************
欢迎使用CSDN论坛专用阅读器 : CSDN Reader(附全部源代码)
http://www.cnblogs.com/feiyun0112/archive/2006/09/20/509783.html
[解决办法]
Create table test(产品ID int,规格 varchar(20),数量 int)
insert into test
Select 1, '1.0*2.0*6000 ',1 union all
Select 2, '1.0*2.0*6000 ',1 union all
Select 3, '1.1*2.0*6000 ',1 union all
Select 4, '1.2*3.2*6000 ',1
Declare @s1 varchar(800),@s2 varchar(1000)
Select @s1= ' ',@s2= ' '
Select @s1=@s1+ ',[ '+ left(规格,3) + ']=sum(case when left(规格,3)= ' ' ' + left(规格,3) + ' ' ' AND SUBSTRING(规格,5,3)= ' ' ' + SUBSTRING(规格,5,3) + ' ' ' then 1 end) '
from test Group By 规格
Select @s2= 'Select SUBSTRING(规格,5,3) '+@s1 + ' From test GRoup By SUBSTRING(规格,5,3) '
exec(@s2)
