SELECT dbo.UOutStorage.FDrawDept,dbo.UOutStorageTable.FMoney,
'人民币'=isnull((select sum(FMoney) from UOutStorageTable where FMoneyType='RMB'),0),
'港币'=isnull((select sum(FMoney) from UOutStorageTable where FMoneyType='HKD'),0),
'CurrencyRate'=(select CurrencyRate from HkCurrency where CurrencyID=1)
--,共折人民币=人民币 + 港币* (1+CurrencyRate)
FROM dbo.UOutStorage INNER JOIN
dbo.UOutStorageTable ON dbo.UOutStorage.FOutId = dbo.UOutStorageTable.FOutId
结果
FDrawDept
001 45.60 121.60 .00 0.88
001 76.00 121.60 .00 0.88
002 76.00 121.60 .00 0.88
002 76.00 121.60 .00 0.88
003 76.00 121.60 .00 0.88 1.怎么列FDrawDept显示不重复的,后面几列做合计是合计所有001的,而不是单独的.002 合计 002 FDrawDept 的 人民币
2.再加个查询列 共折人民币=人民币 + 港币* (1+CurrencyRate) 这个怎么写?
可以这样写,dbo.UOutStorageTable.FMoney这列加个sum()函数,合计那几列加个条件,SQL语句后面加个GROUP BY就行了,
SELECT uos.FDrawDept,'共折人民币'=sum(dbo.UOutStorageTable.FMoney)
'人民币'=isnull((select sum(FMoney) from UOutStorageTable where FMoneyType='RMB' AND FOutId IN (SELECT FOutId FROM UOutStorage WHERE FDrawDept=uos.FDrawDept),0),
'港币'=isnull((select sum(FMoney) from UOutStorageTable where FMoneyType='HKD' AND FOutId IN (SELECT FOutId FROM UOutStorage WHERE FDrawDept=uos.FDrawDept)),0)
FROM dbo.UOutStorage AS uos INNER JOIN
dbo.UOutStorageTable AS uost ON uos.FOutId = uost.FOutId
GROUP BY uos .FDrawDept最后这列不知道是什么意思'CurrencyRate'=(select CurrencyRate from HkCurrency where CurrencyID=1)
'共折人民币'=sum(dbo.UOutStorageTable.FMoney)这样写应该会报错,应为:'共折人民币'=sum(uost.FMoney)