如果计算合同不同币种的毛利

Question

合同表(Contract)
ContractID    ContractNo
1     　　　　Ｃ00001币种表(Currency)
CurrencyID    CurrencyCode
1             RMB
2             USD
3             HKD收入表(Income)
IncomeID  ContractID  CurrencyID     IncomeAmount
1            1          1                1000
2            1          2                2000
3            1          3                3000支出表(Outgo)OutgoID  ContractID  CurrencyID     OutgoAmount
1            1          1                 800
2            1          2                1700
3            1          3                2500应怎样建立视图才能得到如下结果
ContractID   CurrencyCode      ProfitAmount
1             RMB              200
1             USD              300
1             HKD              500
 
或
ContractID   ProfitAmountString
1            RMB200 USD300 HKD500

Answer 1

select t1.ContractID,t3.CurrencyCode,t1.IncomeAmount - t2.OutgoAmount as ProfitAmount
from Income t1, Outgo t2, Currency t3
where t1.ContractID = t2.ContractID and t1.CurrencyID = t2.CurrencyID 
and t1.CurrencyID = t3.CurrencyID

Answer 2

coolingpipe(冷箫轻笛)的方案可能需改进因为在以下状态会得到不正确的结果  
收入表(Income)
IncomeID  ContractID  CurrencyID     IncomeAmount
1            1          1                1000
2            1          2                2000
3            1          3                3000支出表(Outgo)OutgoID  ContractID  CurrencyID     OutgoAmount
1            1          1                15000也就是说收入有RMB、USD、HKD支出只有RMB

Answer 3

也有可能收入只有RMB支出有RMB、USD、HKD的情况

Answer 4

select t3.ContractID,t3.CurrencyCode,
isnull(t1.IncomeAmount,0) - isnull(t2.OutgoAmount,0) as ProfitAmount
from Income t1, Outgo t2, Currency t3
from Currency t3 left join Income t1 on t1.CurrencyID = t3.CurrencyID
                 left join Outgo t2 on t1.ContractID = t2.ContractID and t1.CurrencyID = t2.CurrencyID 

Answer 5

--环境
create table Contract
(
ContractID int,
ContractNo varchar(10)
)create table Currency
(
CurrencyID int,
CurrencyCode varchar(5)
)create table Income
(
IncomeID int,
ContractID int,
CurrencyID int,
IncomeAmount int
)create table Outgo
(
OutgoID int,
ContractID int,
CurrencyID int,
OutgoAmount int
)insert into Contract select 1, 'Ｃ00001'insert into Currency select 1,             'RMB'
insert into Currency select 2,             'USD'
insert into Currency select 3,             'HKD'insert into Income select 1,            1,          1,                1000
insert into Income select 2,            1,          2,                2000
insert into Income select 3,            1,          3,                3000insert into Outgo select 1,            1,          1,                 800
insert into Outgo select 2,            1,          2,                1700
insert into Outgo select 3,            1,          3,                2500
--语句
select t3.ContractID,t3.CurrencyCode,
isnull(t1.IncomeAmount,0) - isnull(t2.OutgoAmount,0) as ProfitAmount
from 
(select * from Currency, Contract)t3 
left join Income t1 on t1.CurrencyID = t3.CurrencyID and t1.ContractID = t3.ContractID
left join Outgo t2 on t3.CurrencyID = t2.CurrencyID and t3.ContractID = t2.ContractID --结果
1 RMB 200
1 USD 300
1 HKD 500

Answer 6

我的意思没有表达清楚，上面方案在以下情况不正确收入表(Income)
IncomeID  ContractID  CurrencyID     IncomeAmount
1            1          1                1000
2            1          2                2000
3            1          3                3000
4            1          1                 100
5            1          2                 200 支出表(Outgo)OutgoID  ContractID  CurrencyID     OutgoAmount
1            1          1                25000
也就是说同一币种有可能有几个收费项目

Answer 7

CREATE TABLE Contract([ContractID] int, [ContractNo] varchar(10))
INSERT INTO Contract
    SELECT  1, 'Ｃ00001'CREATE TABLE Currency([CurrencyID] int, [CurrencyCode] varchar(10))
INSERT INTO Currency
    SELECT  1, 'RMB'
    UNION ALL SELECT 2, 'USD'
    UNION ALL SELECT 3, 'HKD'CREATE TABLE Income([IncomeID] int, [ContractID] int, [CurrencyID] int, [IncomeAmount] int)
INSERT INTO Income
    SELECT  1, 1, 1, 1000
    --UNION ALL SELECT 2, 1, 2, 2000
    UNION ALL SELECT 3, 1, 3, 3000
CREATE TABLE Outgo([OutgoID] int, [ContractID] int, [CurrencyID] int, [OutgoAmount] int)
INSERT INTO Outgo
    SELECT  1, 1, 1, 800
    UNION ALL SELECT 2, 1, 2, 1700
    --UNION ALL SELECT 3, 1, 3, 2500SELECT C.ContractID, C.CurrencyCode, INO.IncomeAmount - INO.OutgoAmount AS ProfitAmount
FROM (SELECT * FROM Contract, Currency)C
LEFT JOIN
(SELECT CASE WHEN I.ContractID IS NOT NULL THEN I.ContractID ELSE O.ContractID END AS ContractID,
CASE WHEN I.CurrencyID IS NOT NULL THEN I.CurrencyID ELSE O.CurrencyID END AS CurrencyID,
ISNULL(I.IncomeAmount, 0) AS IncomeAmount, ISNULL(OutgoAmount, 0) AS OutgoAmount
FROM Income I FULL JOIN Outgo O
ON I.ContractID = O.ContractID AND I.CurrencyID = O.CurrencyID)INO
ON C.ContractID = INO.ContractID AND C.CurrencyID = INO.CurrencyIDDROP TABLE Contract, Currency, Income, Outgo

Answer 8

hhhdyj(萤火虫) 的方案在同一币种有多个收费项目的情况下也不正确

Answer 9

这样修改
SELECT C.ContractID, C.CurrencyCode, INO.IncomeAmount - INO.OutgoAmount AS ProfitAmount
FROM (SELECT * FROM Contract, Currency)C
LEFT JOIN
(SELECT CASE WHEN I.ContractID IS NOT NULL THEN I.ContractID ELSE O.ContractID END AS ContractID,
CASE WHEN I.CurrencyID IS NOT NULL THEN I.CurrencyID ELSE O.CurrencyID END AS CurrencyID,
ISNULL(I.IncomeAmount, 0) AS IncomeAmount, ISNULL(OutgoAmount, 0) AS OutgoAmount
FROM (SELECT ContractID, CurrencyID, SUM(IncomeAmount) AS IncomeAmount FROM Income GROUP BY ContractID, CurrencyID)I
FULL JOIN (SELECT ContractID, CurrencyID, SUM(OutgoAmount) AS OutgoAmount FROM Outgo GROUP BY ContractID, CurrencyID)O
ON I.ContractID = O.ContractID AND I.CurrencyID = O.CurrencyID)INO
ON C.ContractID = INO.ContractID AND C.CurrencyID = INO.CurrencyID

Answer 10

收入表(Income)
IncomeID  ContractID  CurrencyID     IncomeAmount
1            1          1                1000
2            1          2                2000
3            1          3                3000
4            1          1                 100
5            1          2                 200 支出表(Outgo)OutgoID  ContractID  CurrencyID     OutgoAmount
1            1          1                25000
也就是说同一币种有可能有几个收费项目那这种数据你需要的结果是什么呢？
--环境
create table Contract
(
ContractID int,
ContractNo varchar(10)
)create table Currency
(
CurrencyID int,
CurrencyCode varchar(5)
)create table Income
(
IncomeID int,
ContractID int,
CurrencyID int,
IncomeAmount int
)create table Outgo
(
OutgoID int,
ContractID int,
CurrencyID int,
OutgoAmount int
)insert into Contract select 1, 'Ｃ00001'insert into Currency select 1,             'RMB'
insert into Currency select 2,             'USD'
insert into Currency select 3,             'HKD'insert into Income select 1,            1,          1,                1000
insert into Income select 2,            1,          2,                2000
insert into Income select 3,            1,          3,                3000
insert into Income select 4,            1,          1,                 100
insert into Income select 5,            1,          2,                 200 insert into Outgo select 1,            1,          1,                25000
--语句
select t3.ContractID,t3.CurrencyCode,
isnull(t1.IncomeAmount,0) - isnull(t2.OutgoAmount,0) as ProfitAmount
from 
(select * from Currency, Contract)t3 
left join (select ContractID,CurrencyID,sum(IncomeAmount) as IncomeAmount from Income group by  ContractID,CurrencyID) t1 
           on t1.CurrencyID = t3.CurrencyID and t1.ContractID = t3.ContractID
left join (select ContractID,CurrencyID,sum(OutgoAmount) as OutgoAmount from Outgo group by ContractID,CurrencyID) t2
            on t3.CurrencyID = t2.CurrencyID and t3.ContractID = t2.ContractID --结果
1 RMB -23900
1 USD 2200
1 HKD 3000--是这样的结果吗？