有两个表，如下所示：
财务：
policy_id state coverage earned_premium_dollar_amount
1 OH bi 1.7
1 OH umuim 0.82
2 OH coll 13.34
3 TX coll 13.28
6 TX comp 6.55
`
`
`
地区：
state bi pd comp coll
TX 1.01 1.12 0.82 0.93
TX 1.88 1.39 2.63 1.32
OH 1.87 1.29 2.1 1.02
`
`
`
目标是：
-
首先，我们需要计算bi，pd，comp和coll的平均值，这些平均值分别按“州”分组为表格区域中的区域因子。由于“地域”表仅包含4个coverage，因此我们假定“地域”表中未表示的coverage的系数为1。
-
第二，创建一个名为new_earned_premium_dollar_amount的新列，方法是分别将覆盖范围除以州领土因子。例如，在财务表中，第一列的覆盖范围是bi，状态是OH，因此我们需要使用1.7除以领土表中OH的bi的平均数。
我编写了一个SQL查询来确定覆盖率何时为“ coll”，如下所示：
select *,f.earned_premium_dollar_amount/t.avgcoll as new_earned_premium_dollar_amount
from (select *
from finacials) f
left join
(select state,cast(AVG(bi) as decimal(38,2)) avgbi,cast(AVG(pd) as decimal(38,2)) avgpd,cast(AVG(comp) as decimal(38,2)) avgcomp,cast(AVG(coll) as decimal(38,2)) avgcoll
from territories
group by state) t
on t.state=f.state
where coverage='coll'
结果是：
policy_id state coverage earned_premium_dollar_amount state avgbi avgpd avgcomp avgcoll new_earned_premium_dollar_amount
2 OH coll 13.34 OH 1.77 1.59 2.32 1.45 9.2
4 OH coll 18.16 OH 1.77 1.59 2.32 1.45 12.5241379310345
8 OH coll 1.13 OH 1.77 1.59 2.32 1.45 0.779310344827586
12 OH coll 18.16 OH 1.77 1.59 2.32 1.45 12.5241379310345
2 TX coll 13.34 TX 1.53 1.29 1.97 1.22 10.9344262295082
`
`
`
我只有'coll'的结果。如何获得其余结果并将其合并？同样，我可以通过以下代码获得“ pd”的结果：
select *,f.earned_premium_dollar_amount/t.avgpd as new_earned_premium_dollar_amount
from (select *
from finacials) f
left join
(select state,2)) avgcoll
from territories
group by state) t
on t.state=f.state
where coverage='pd'
结果是：
policy_id state coverage earned_premium_dollar_amount state avgbi avgpd avgcomp avgcoll new_earned_premium_dollar_amount
4 OH pd 8.91 OH 1.77 1.59 2.32 1.45 5.60377358490566
11 OH pd 1.3 OH 1.77 1.59 2.32 1.45 0.817610062893082
问题：
我想我可能会使用join来加入那些结果，但是我不知道该怎么做。 或者，有没有其他方法可以实现我的目标？
···················································· ·································
感谢Bjeran的回答。这段代码有效：
select *,case
WHEN f.coverage = 'bi' THEN f.earned_premium_dollar_amount/t.avgbi
WHEN f.coverage = 'pd' THEN f.earned_premium_dollar_amount/t.avgpd
WHEN f.coverage = 'comp' THEN f.earned_premium_dollar_amount/t.avgcomp
WHEN f.coverage = 'coll' THEN f.earned_premium_dollar_amount/t.avgcoll
else f.earned_premium_dollar_amount/1
end as 'new_earned_premium_dollar_amount'
from (select *
from finacials) f
left join
(select state,2)) avgcoll
from territories
group by state) t
on t.state=f.state
最终结果如下所示：
现在，唯一的问题是状态列出现两次。我可能会考虑使用其他联接，而不是左联接。