--查询1
select f_bi
,参数1=sum(case f_bn when '参数1' then f_bv end)
,参数2=sum(case f_bn when '参数2' then f_bv end)
,参数3=sum(case f_bn when '参数3' then f_bv end)
,参数4=sum(case f_bn when '参数4' then f_bv end)
from 表
where f_bi in('a','b','c','d')
group by f_bi--查询2
select f_bi
,参数1=sum(case f_bn when '参数1' then f_bv end)
,参数1=sum(case f_bn when '参数5' then f_bv end)
from 表
where f_bi in('a','e')
group by f_bi
select f_bi
,参数1=sum(case f_bn when '参数1' then f_bv end)
,参数2=sum(case f_bn when '参数2' then f_bv end)
,参数3=sum(case f_bn when '参数3' then f_bv end)
,参数4=sum(case f_bn when '参数4' then f_bv end)
from 表
where f_bi in('a','b','c','d')
group by f_bi--查询2
select f_bi
,参数1=sum(case f_bn when '参数1' then f_bv end)
,参数1=sum(case f_bn when '参数5' then f_bv end)
from 表
where f_bi in('a','e')
group by f_bi
select f_bi
,参数1=cast(sum(case f_bn when '参数1' then f_bv end) as decimal(10,2))
,参数2=cast(sum(case f_bn when '参数2' then f_bv end) as decimal(10,2))
,参数3=cast(sum(case f_bn when '参数3' then f_bv end) as decimal(10,2))
,参数4=cast(sum(case f_bn when '参数4' then f_bv end) as decimal(10,2))
from 表
where f_bi in('a','b','c','d')
group by f_bi--查询2
select f_bi
,参数1=cast(sum(case f_bn when '参数1' then f_bv end) as decimal(10,2))
,参数1=cast(sum(case f_bn when '参数5' then f_bv end) as decimal(10,2))
from 表
where f_bi in('a','e')
group by f_bi
@参数 varchar(1000),
@行 varchar(8000)
as
declare @s varchar(8000),@i int
select @参数=@参数+',',@行=''''+replace(@行,',',''',''')+''''
,@i=charindex(',',@参数),@s=''
while @i>0
begin
select @s=@s+','+left(@参数,@i-1)
+'=cast(sum(case f_bn when '''
+left(@参数,@i-1)+''' then f_bv end) as decimal(10,2))'
,@参数=substring(@参数,@i+1,1000)
,@i=charindex(',',@参数)
endexec('select f_bi'+@s+'
from 表
where f_bi in('+@行+')
group by f_bi
')
goexec p_qry '参数1,参数2,参数3,参数4','a,b,c,d'
exec p_qry '参数1,参数5','a,e'
go
create table 表(f_bi varchar(20),f_bn varchar(20),f_bv float)
insert 表 select 'a','参数1',3.56
union all select 'a','参数2',1.52
union all select 'a','参数3',7.13
union all select 'b','参数1',9.41
union all select 'b','参数2',2.61
union all select 'b','参数4',8.03
union all select 'c','参数1',9.56
union all select 'c','参数2',3.76
union all select 'c','参数4',3.54
union all select 'd','参数1',0.85
union all select 'd','参数2',2.13
union all select 'd','参数3',1.23
union all select 'e','参数5',65.4
union all select 'e','参数6',67.6
go--通用的存储过程
create proc p_qry
@参数 varchar(1000),
@行 varchar(8000)
as
declare @s varchar(8000),@i int
select @参数=@参数+',',@行=''''+replace(@行,',',''',''')+''''
,@i=charindex(',',@参数),@s=''
while @i>0
begin
select @s=@s+','+left(@参数,@i-1)
+'=cast(sum(case f_bn when '''
+left(@参数,@i-1)+''' then f_bv end) as decimal(10,2))'
,@参数=substring(@参数,@i+1,1000)
,@i=charindex(',',@参数)
endexec('select f_bi'+@s+'
from 表
where f_bi in('+@行+')
group by f_bi
')
goexec p_qry '参数1,参数2,参数3,参数4','a,b,c,d'
exec p_qry '参数1,参数5','a,e'
go--删除测试
drop table 表
drop proc p_qry/*--测试结果
f_bi 参数1 参数2 参数3 参数4
-------------------- ------------ ------------ ------------ ------------
a 3.56 1.52 7.13 NULL
b 9.41 2.61 NULL 8.03
c 9.56 3.76 NULL 3.54
d .85 2.13 1.23 NULL(所影响的行数为 4 行)f_bi 参数1 参数1
-------------------- ------------ ------------
a 3.56 NULL
e NULL 65.40(所影响的行数为 2 行)
--*/
看看T-SQL帮助吧,官方有这种类型的权威做法在大数据量时候尤其能显示出交叉表的效率1.
SELECT f_bi,
SUM(CASE f_bn WHEN '参数1' THEN f_bv ELSE NULL END) AS 参数1,
SUM(CASE f_bn WHEN '参数2' THEN f_bv ELSE NULL END) AS 参数2,
SUM(CASE f_bn WHEN '参数3' THEN f_bv ELSE NULL END) AS 参数3,
SUM(CASE f_bn WHEN '参数4' THEN f_bv ELSE NULL END) AS 参数4
FROM table
where f_bi in('a','b','c','d')
GROUP BY f_bi2.
SELECT f_bi,
SUM(CASE f_bn WHEN '参数1' THEN f_bv ELSE NULL END) AS 参数1,
SUM(CASE f_bn WHEN '参数5' THEN f_bv ELSE NULL END) AS 参数5
FROM table
where f_bi in('a','e')
GROUP BY f_bi
感谢邹健 和 ccmoon(IzuaL) 两位大哥帮了小弟的忙。
愿同 john shu 和 wea1978(川) 一道学习!