如何连接具有 2 个不同 id 的 3 个表（雪花中的 sql）？

Question

首先，我需要从一个表中解压缩数据以转换为一个新表。其次，我需要根据客户 ID Z 将另一个表连接到这个新表。第三，我需要使用不同的客户 ID（客户 ID Y）将另一个表连接到第二个表。

第 1 部分：我从表中获取此数据下面一个。

名称（第 1 列）	值（第 2 列）	Customer_idZ（第 3 列）
account_status	ACTIVE	1234
card_template	奖励	1234
customer_creation_date	1/8/2022	1234
enroll_store_code	pxweb	1234
enroll_store_name	网站	1234
Push_opt_in	是	1234

我正在转换表 A 中的数据到一个新表，格式如下，代码如下：

'''SELECT
Customer_idZ,
MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
 FROM Table A
GROUP BY Customer_idZ;

Customer_idZ	account_status	card_template	customer_creation_date	enroll_store_code	enroll_store_name	push_opt_in
1234	ACTIVE	PX Rewards	1/8/2022	pxweb	Web Site	Yes

第 2 部分：然后我想将另一个表（表 B）加入到这个新格式化的表中基于共享 customer_idZ 字段的数据。表B中的数据如下；

Customer_idZ	状态	Customer_idY
1234	ACTIVE	567890

第 3 部分：我需要根据共享的 Customer_idY 字段将另一个表（表 C）连接到表 B。表C中的数据如下；

Customer_idY	Household_size	Children_present_in_household
567890	6	是

最终：我只需要最终输出作为 customer_idY 和 customer_idZ 的列表。

我不确定如何构建三个表之间所需的多连接，并且需要为表 a 中具有“活动状态”且家庭规模为 4 人或以上的配置文件提取 id

Answer 1

我刚刚通过 @xQbert 更改了查询中的列名称，希望它有所帮助。

SELECT A.Customer_idZ
     , B.Customer_idY
     , account_status
     , card_template
     , customer_creation_date
     , enroll_store_code
     , enroll_store_name
     , push_opt_in
     , Household_size
     , Children_present_in_household
FROM (SELECT
      Customer_idZ,
      MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
      MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
      MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
      MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
      MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
      MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
      FROM  A
      GROUP BY Customer_idZ) A
INNER JOIN B
 on A.Customer_idZ = B.Customer_idZ
INNER JOIN C
 on B.Customer_idY = C.Customer_idY;

Answer 2

我正在使用通用表表达式（CTE）“NEWA”来重新格式化您的“A”表...我们可以将其设为内联视图，但我发现 CTE 更易于阅读

我们可能需要更改 INNER JOIN< /code> 到 LEFT OUTER JOIN 取决于当相关记录不在关联表中时您想要如何处理...

With NewA As (SELECT
Customer_idZ,
MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
 FROM Table A
GROUP BY Customer_idZ)

SELECT Cusotmer_idZ
     , Customer_idY
     , account_status
     , card_template
     , customer_creation_date
     , enroll_store_code
     , enroll_store_name
     , push_opt_in
     , Household_size
     , Children_present_in_household
FROM NewA A
INNER JOIN B
 on A.Customer_idZ= B.Customer_idZ
INNER JOIN C
 on B.Customer_IDY = C.Customer_IDY
WHERE Household_size >= 4
  and account_status='Active'

然后，我们使用常见的联接语法来联接表之间的关系。

现在，如果您想要 A 中的所有记录，并且只需要 B 中的匹配记录，并且 A 中的所有记录组合 b 和 C 中的相关记录，那么我们可以进行这些外联接

。或者我们可以使用完整外联接返回所有表中的所有记录。只是取决于你想要什么。

我们还可以使用内联视图代替 CTE：

SELECT A.Cusotmer_idZ
     , B.Customer_idY
     , account_status
     , card_template
     , customer_creation_date
     , enroll_store_code
     , enroll_store_name
     , push_opt_in
     , Household_size
     , Children_present_in_household
FROM (SELECT
      Customer_idZ,
      MAX(CASE WHEN Name = 'account_status' THEN VALUE END) AS account_status,
      MAX(CASE WHEN Name = 'card_template' THEN VALUE END) AS card_template,
      MAX(CASE WHEN Name = 'customer_creation_date' THEN VALUE END) AS customer_creation_date,
      MAX(CASE WHEN Name = 'enroll_store_code' THEN VALUE END) AS enroll_store_code,
      MAX(CASE WHEN Name = 'enroll_store_name' THEN VALUE END) AS enroll_store_name,
      MAX(CASE WHEN Name = 'push_opt_in' THEN VALUE END) AS push_opt_in
      FROM Table A
      GROUP BY Customer_idZ) A
INNER JOIN B
 on A.Customer_idZ= B.Customer_idZ
INNER JOIN C
 on B.Customer_IDY = C.Customer_IDY
WHERE Household_size >= 4
  and account_status='Active'