select * from ( select OptNO as 编号, isnull (case currency when 'RMB'then rtrim(sum(case inout when 'in' then PreMoney else 0 end))end,'0.00') as 人民币收入, isnull (case currency when 'RMB'then rtrim(sum(case inout when 'Out' then PreMoney else 0 end))end,'0.00') as 人民币支出, isnull (case currency when 'USD'then rtrim(sum(case inout when 'in' then PreMoney*8 else 0 end))end,'0.00') as 美金收入, isnull (case currency when 'USD'then rtrim(sum(case inout when 'Out' then PreMoney*8 else 0 end))end,'0.00') as 美金支出 from FeeResult where OptNO ='SPCJXEE06071111' and feetype='单票' group by OPTNO,CURRENCY )T group by CURRENCY
OptNO currency 汇率 PreMoney 这样就简单多了,而且这样也更符合设计要求,不然汇率一更改的话以前的汇率和现在的汇率又不好区分了~~~
您看看我这个语句为什么如果currency只有RMB或只有USD,是显示一行结果的,但如果既有RMB又有USD,还是显示两行呢?
你的方法对于只有一个是好用的,那么就把源数据进行一下处理,然后再用你的方法。
(
select
OptNO as 编号,
isnull (case currency
when 'RMB'then
rtrim(sum(case inout
when 'in' then PreMoney else 0 end))end,'0.00') as 人民币收入,
isnull (case currency
when 'RMB'then
rtrim(sum(case inout
when 'Out' then PreMoney else 0 end))end,'0.00') as 人民币支出,
isnull (case currency
when 'USD'then
rtrim(sum(case inout
when 'in' then PreMoney*8 else 0 end))end,'0.00') as 美金收入,
isnull (case currency
when 'USD'then
rtrim(sum(case inout
when 'Out' then PreMoney*8 else 0 end))end,'0.00') as 美金支出
from FeeResult where OptNO ='SPCJXEE06071111' and feetype='单票'
group by OPTNO,CURRENCY
)T group by CURRENCY