mysql - 填充表格中缺失的行

Question

起初我的问题可能看起来很简单并且之前已经被问过。请耐心等待 - 我认为这是一个独特的问题。

表 A 有列 State , County , Month , Year , 和 Rate .每个州、县的复合 Material 都以不同的日期和比率多次列出。有些行设置了州和县，该行中的其他所有内容均为 NULL。

表 B 有每个月和每年的违约率。列是 Month , Year , 和 Rate .在这张表中，我有几年的默认数据。

因此，对于表 A 中的每个州、县组合，我想用表 B 中的数据填充任何缺失的数据。

我创建了一个看起来像表 A 的表 C，除了所有数据都用表 B 中的默认数据填充。然后我尝试将表 A 和表 C 联合在一起。但我最终遇到了两个问题。

首先，我最终得到了重复的行，除了比率之外，其他所有内容都相同。在这种情况下，我只想保留最初在表 A 中的行(而不是“默认率”)。

其次，我以设置了州和县但其他所有内容均为 NULL 的行结束。我需要将这些行替换为每个违约率的行。

所以最后我想为每个 State,County,Month,Year 组合各占一行。

是否可以按照我所描述的那样组合表格。

如果您需要任何澄清，请告诉我。谢谢。

表 A 有几千行。每个州、县组合的 1 到 48 行:

+-------+--------+-------+------+------+| State | County | Month | Year | Rate |+-------+--------+-------+------+------+| NY    | Albany | 1     | 2011 | ###  || NY    | Albany | 2     | 2011 | ###  |...| NY    | Albany | 12    | 2011 | ###  || NY    | Albany | 1     | 2012 | ###  |...| NY    | Albany | 12    | 2012 | ###  || NY    | Monroe | 1     | 2011 | ###  |...| NY    | Monroe | 12    | 2011 | ###  || NY    | Essex  | NULL  | NULL | NULL |+-------+--------+-------+------+------+

表 B 有 36 行。 3 年内每个月一行:

+-------+------+------+| Month | Year | Rate |+-------+------+------+| 1     | 2011 | ***  || 2     | 2011 | ***  || ...   |      |      || 12    | 2011 | ***  || 1     | 2012 | ***  || ...   |      |      || 12    | 2012 | ***  || 1     | 2013 | ***  || ...   |      |      || 12    | 2013 | ***  |+-------+------+------+

结果表的行数比表 A 多。每个 State,County 组合至少有默认表中的 36 行:

+-------+--------+-------+------+------+| State | County | Month | Year | Rate |+-------+--------+-------+------+------+| NY    | Albany |     1 | 2011 | ###  || ...   |        |       |      |      || NY    | Albany |    12 | 2011 | ###  || NY    | Albany |     1 | 2012 | ###  || ...   |        |       |      |      || NY    | Albany |    12 | 2012 | ###  || NY    | Albany |     1 | 2013 | ***  || ...   |        |       |      |      || NY    | Albany |    12 | 2013 | ***  || NY    | Monroe |     1 | 2011 | ###  || ...   |        |       |      |      || NY    | Monroe |    12 | 2011 | ###  || NY    | Monroe |     1 | 2012 | ***  || ...   |        |       |      |      || NY    | Monroe |    12 | 2012 | ***  || NY    | Monroe |     1 | 2013 | ***  || ...   |        |       |      |      || NY    | Monroe |    12 | 2013 | ***  || NY    | Essex  |     1 | 2011 | ***  || ...   |        |       |      |      || NY    | Essex  |    12 | 2011 | ***  || NY    | Essex  |     1 | 2012 | ***  || ...   |        |       |      |      || NY    | Essex  |    12 | 2012 | ***  || NY    | Essex  |     1 | 2013 | ***  || ...   |        |       |      |      || NY    | Essex  |    12 | 2013 | ***  |+-------+--------+-------+------+------+

key :***是默认表中的比率。 ###是另一个表的费率

Answer 1

我认为最好的方法是生成地理和时间的所有组合。您可以通过从 tablea 中获取 state 和 county 并与 year 和 交叉连接来做到这一点来自 tableb 的月份。然后用left join查看tablea有没有值。如果是这样，请选择它。否则，从 tableb 中取值:

select sc.state, sc.county, ym.year, ym.month, coalesce(a.rate, ym.rate) as rate
from (select distinct state, county from tablea) sc cross join
     tableb ym left outer join
     tablea a
     on a.state = sc.state and a.county = sc.county and
        a.year = ym.year and a.month = ym.month ;

+7 个字符