取消查看SQL中的表和联接条件

Question

我有两个表：

资金

country   2002     2006    2010   2014
Chicago   1000    23000    4500   9076

奖牌

year   sport   event    country   gender   medal_rank  medal
2006    A        Z      Chicago    F          2         silver
1924    B        ZZ     Newyork    M          3         bronze
2014    C        ZZZZ   Chicago    M          1         Gold
1967    B        ZZb    Newyork    M          3         bronze
2014    C        ZZa    Chicago    F          2         silver

我想取消像这样的基金中的数据

country    year    value
Chicago    2002    1000
Chicago    2006    23000
Chicago    2010    4500
Chicago    2014    9076

然后加入Medals，只计算2002年、2006年、2010年和2104年的奖牌数，因为我们在Funds中只有这些年的价值。在最后一步中，我将value/countofmedal划分为value/mearch。

最后的表格应该是这样的：

country    year    value     countofmedeals      value/medal
Chicago    2002    1000        0                     0
Chicago    2006    23000       1                     23000
Chicago    2010    4500        0                     0
Chicago    2014    9076        2                     4536

下面是我尝试过的代码，但它不起作用。

use RDG
select
S.Yr,
qu.year,
s.Cost,
count(qu.medal)
from
(
select 
country,
yr, 
Cost
from Funds
unpivot
(
Yr for Cost in ([2002],[2006],[2010],[2014])
) as u
) s
join medals qu
on (s.country=qu.country and s.Yr=qu.year)
group by s.Yr,qu.year,s.Cost

Answer 1

declare @Funds table
(
country varchar(20),
[2002] int,
[2006] int,
[2010] int,
[2014] int
);
insert into @Funds(country, [2002], [2006], [2010], [2014])
values ('Chicago', 1000, 23000, 4500, 9076);

declare @Medals table
(
year int,
sport varchar(20),
event varchar(20),
country varchar(20),
gender char(1),
medal_rank tinyint,
medal varchar(20)
);
insert into @Medals(year, sport, event, country, gender, medal_rank, medal)
values
(2006, 'A', 'Z', 'Chicago', 'F', 2, 'silver'),
(1924, 'B', 'ZZ', 'Newyork', 'M', 3, 'bronze'),
(2014, 'C', 'ZZZZ', 'Chicago', 'M', 1, 'Gold'),
(1967, 'B', 'ZZb', 'Newyork', 'M', 3, 'bronze'),
(2014, 'C', 'ZZa', 'Chicago', 'F', 2, 'silver');
select
S.country,
S.Yr,
qu.year /*??*/,
s.value,
count(qu.medal) as countofmedals,
cast(isnull(s.value*1./nullif(count(qu.medal), 0), 0) as decimal(19,2)) as [value/medal]
from
(
select 
country,
yr, 
value
from @Funds
unpivot
(
value for yr in ([2002],[2006],[2010],[2014]) --!!!!
) as u
) s
left join @medals qu --!!!!
on (s.country=qu.country and s.Yr=qu.year)
group by s.country, s.Yr,qu.year/*??*/,s.value;