日期:2014-05-19  浏览次数:20607 次

这个查询怎么写,谢谢
表结构是这样的:
T1,T2,T3…T12,A1,A2,A3…A12,B1,B2,B3…B12
希望查询出的结果是:
如果T1-T12中只有一个有值,那么就用这个值乘上3.14
如果T1-T12中有复数个值,那么用其中最大的值乘上3.14

A1-A12,B1-B12的处理和T1-T12的处理相同

就输出乘上3.14以后三个值。

用一个sql语句能查询出来么?

谢谢

------解决方案--------------------
前提,至少保证有一个值

select id , max(value) * 3.14 as value from
(
select id = 'T ' , t1 as value
union all
select id = 'T ' , t2 as value
.....
select id = 'T ' , t12 as value
union all
select id = 'A ' , t1 as value
union all
select id = 'A ' , t2 as value
.....
select id = 'A ' , t12 as value
union all
select id = 'B ' , t1 as value
union all
select id = 'B ' , t2 as value
.....
select id = 'B ' , t12 as value
) T
group by id



------解决方案--------------------
我也理解错了

--创建测试环境
create table #t(a1 numeric(18,2),a2 numeric(18,2),a3 numeric(18,2),b1 numeric(18,2),b2 numeric(18,2)
,b3 numeric(18,2),c1 numeric(18,2),c2 numeric(18,2),c3 numeric(18,2),num1 numeric(18,2)
,num2 numeric(18,2),num3 numeric(18,2))

--插入测试数据
insert #t(a1,a2,a3,b1,b2,b3,c1,c2,c3)
select '1 ', '2 ', '3 ', '10 ',null,null, '5 ', '4 ',null union all
select '5 ', '2 ',null, '5 ', '3 ', '9 ', '1 ',null,null

--求解过程
declare @num1 numeric(18,2),@num2 numeric(18,2),@num3 numeric(18,2),@i numeric(18,2)

update #t
set @num1 = 0,@num2 = 0,@num3 = 0
,@i = a1
,@i = case when @i < a2 or @i is null then a2 else @i end
,@i = case when @i < a3 or @i is null then a3 else @i end
,@num1 = @num1 + isnull(@i,0)*3.14
,@i = b1
,@i = case when @i < b2 or @i is null then b2 else @i end
,@i = case when @i < b3 or @i is null then b3 else @i end
,@num2 = @num2 + isnull(@i,0)*3.14
,@i = c1
,@i = case when @i < c2 or @i is null then c2 else @i end
,@i = case when @i < c3 or @i is null then c3 else @i end
,@num3 = @num3 + isnull(@i,0)*3.14
,num1 = @num1,num2 = @num2,num3 = @num3

select * from #t

--删除测试环境
drop table #t

/*--测试结果
a1 a2 a3 b1 b2 b3 c1 c2 c3 a b c
1.00 2.00 3.00 10.00 NULL NULL 5.00 4.00 NULL 9.42 31.40 15.70
5.00 2.00 NULL 5.00 3.00 9.00 1.00 NULL NULL 15.70 28.26 3.14


*/