select
aaa as 编号,
isnull(rtrim(sum(case inout
when 'in' then bbb else 0 end)),'0.00') as 人民币收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb else 0 end)),'0.00') as 人民币支出,
isnull(rtrim(sum(case inout
when 'in' then bbb*8 else 0 end)),'0.00') as 美金收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb*8 else 0 end)),'0.00') as 美金支出
from tabel where aaa ='XXXXX' and ddd='单票'and mmm='RMB'
group by optno,inout
aaa as 编号,
isnull(rtrim(sum(case inout
when 'in' then bbb else 0 end)),'0.00') as 人民币收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb else 0 end)),'0.00') as 人民币支出,
isnull(rtrim(sum(case inout
when 'in' then bbb*8 else 0 end)),'0.00') as 美金收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb*8 else 0 end)),'0.00') as 美金支出
from tabel where aaa ='XXXXX' and ddd='单票'and mmm='RMB'
group by optno,inout
aaa as 编号,
isnull(rtrim(sum(case inout
when 'in' then bbb else 0 end)),'0.00') as 人民币收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb else 0 end)),'0.00') as 人民币支出,
isnull(rtrim(sum(case inout
when 'in' then bbb*8 else 0 end)),'0.00') as 美金收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb*8 else 0 end)),'0.00') as 美金支出
from tabel where aaa ='XXXXX' and ddd='单票'and mmm='RMB'
group by aaa说明下:optno是什么字段
optno就是aaa
成了,谢谢DX,是因为group by 的关系?
aaa as 编号,
isnull(rtrim(case inout
when 'in' then sum(bbb)end),'0.00')as 人民币收入,
isnull(rtrim(case inout
when 'Out' then sum(bbb)end),'0.00')as 人民币支出,
isnull(rtrim(case inout
when 'in' then sum(bbb)*8 end),'0.00')as 美金收入,
isnull(rtrim(case inout
when 'out' then sum(bbb)*8 end),'0.00')as 美金支出
from tabel where aaa ='XXXXX' and ddd='单票'and mmm='RMB'
group by optno,inout
或
select dictinct
aaa as 编号,
isnull(rtrim(case inout
when 'in' then sum(bbb)end),'0.00')as 人民币收入,
isnull(rtrim(case inout
when 'Out' then sum(bbb)end),'0.00')as 人民币支出,
isnull(rtrim(case inout
when 'in' then sum(bbb)*8 end),'0.00')as 美金收入,
isnull(rtrim(case inout
when 'out' then sum(bbb)*8 end),'0.00')as 美金支出
from tabel where aaa ='XXXXX' and ddd='单票'and mmm='RMB'
group by optno,inout
或
select * from
(select
aaa as 编号,
isnull(rtrim(sum(case inout
when 'in' then bbb else 0 end)),'0.00') as 人民币收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb else 0 end)),'0.00') as 人民币支出,
isnull(rtrim(sum(case inout
when 'in' then bbb*8 else 0 end)),'0.00') as 美金收入,
isnull(rtrim(sum(case inout
when 'Out' then bbb*8 else 0 end)),'0.00') as 美金支出
from tabel where aaa ='XXXXX' and ddd='单票'and mmm='RMB'
group by optno,inout) t
group by aaa
既:只有RMB,则美金收入和美金支出计为0.00
只有USD,则人民币收入和支出计为0.00
既有RMB又有USD的话,分别计算所以WHERE后面的条件里是不是就不能把mmm的条件写死啊???该如何写呢???