配置单元sql-我有两个表。调查及调查意见及结构如下：
create external table if not exists survey(
id string,
category_name string,
subcategory_name string)
STORED AS parquet;
insert into survey(id, category_name, subcategory_name)
values ('1', 'Engine', 'Engine problem other than listed');
insert into survey(id, category_name, subcategory_name)
values ('1', 'Exterior Body', 'Color match of painted parts');
insert into survey(id, category_name, subcategory_name)
values ('1', 'Exterior Body', 'Tail lights');
insert into survey(id, category_name, subcategory_name)
values ('1', 'Heating/Ventilation and Cooling', 'Front windshield fogs up');
insert into survey(id, category_name, subcategory_name)
values ('1', 'Transmission', 'Rough shifting');
create external table if not exists survey_comments(
id string,
category_name_txt string,
subcategory_name_txt string,
comments string)
STORED AS parquet;
insert into survey_comments(id, category_name_txt, subcategory_name_txt)
values ('1', 'Exterior Body', 'Tail lights', 'Moisture in lower portion of rear tail lights along with leaves etc.');
insert into survey_comments(id, category_name_txt, subcategory_name_txt)
values ('1', 'Heating/Ventilation and Cooling', 'Front windshield fogs up', 'Small amount of fog low on front windshield during/after rain.');
insert into survey_comments(id, category_name_txt, subcategory_name_txt)
values ('1', 'Miscellaneous', 'General problem other than listed', 'When filling vehicle with gas; the pumps fill the gas line too quickly, had to hold the pump handle only 1/2 way on.');
insert into survey_comments(id, category_name_txt, subcategory_name_txt)
values ('1', 'Miscellaneous', 'General problem other than listed', 'Touch-up paint too red, not same red as on the car.');现在我的完全外部连接如下所示：
select b.id, b.category_name, b.subcategory_name, a.category_name_txt, a.sub_category_name_txt, a.comments
from survey b full outer join survey_comments a
on (
b.id = a.id and
b.category_name = a.category_name_txt and b.subcategory_name = a.sub_category_name_txt
)我没有在survey\u comment\u txt中获得类别名称为“miscellaneous”的行。我需要在调查和调查中的非匹配行作为单独的行和匹配行以及注解。我做错了什么。
1条答案
按热度按时间gopyfrb31#
使用ctes进行测试，发现了两个问题。第一：你在调查评论中插入了四列，但只命名了三列。第二：在查询列名中应该是子类别名称，而不是子类别名称。
固定后试验：
退货：