sql - 将 4 个表中的数据合并到一个列/字段中

Question

我需要在 MS Access 2010 中创建一个查询，将来自 4 个不同表的数据合并到一列中。

表格定义如下；

Table 1: date, country, channel, calls_in
Table 2: date, country, channel, calls_out
Table 3: date, country, channel, email
Table 4: date, country, channel, chat

查询应该如下所示:

Query 1: Date, country, channel, contacts 

联系人列应针对适当的日期、国家/地区和 channel 组合 4 种联系方式(即呼入/呼出、电子邮件和聊天)。

所有 4 个表格都有相同的日期和国家/地区。 channel 特定于每个表。

我一直在努力完成这件事，但无法理解它。

Answer 1

你可能会在这之后:

SELECT
  all_contacts.date,
  all_contacts.country,
  all_contacts.channel,
  "calls in: " & [calls_in] & ", calls out: " & [calls_out] & ", email: " & [email] & ", chat " & [chat] AS contacts
FROM
  ((((select date, country, channel from [Table 1] union
      select date, country, channel from [Table 2] union
      select date, country, channel from [Table 3] union
      select date, country, channel from [Table 4])  AS all_contacts
  LEFT JOIN [Table 1] ON (all_contacts.channel = [Table 1].channel) AND (all_contacts.country = [Table 1].country) AND (all_contacts.date = [Table 1].date))
  LEFT JOIN [Table 2] ON (all_contacts.channel = [Table 2].channel) AND (all_contacts.country = [Table 2].country) AND (all_contacts.date = [Table 2].date))
  LEFT JOIN [Table 3] ON (all_contacts.channel = [Table 3].channel) AND (all_contacts.country = [Table 3].country) AND (all_contacts.date = [Table 3].date))
  LEFT JOIN [Table 4] ON (all_contacts.channel = [Table 4].channel) AND (all_contacts.country = [Table 4].country) AND (all_contacts.date = [Table 4].date);

由于 MS-Access 不支持 FULL OUTER JOINS，并且没有像 GROUP_CONCAT 这样的聚合函数，我只能加入一个 UNION 子查询，其中包含每个表的所有日期、国家和 channel ，并且我'然后将所有联系人(calls_in、calls_out、电子邮件和聊天)合并到一个单元格中。