HIVE字段嵌套子查询

在Hive中，可以使用嵌套子查询来处理字段。嵌套子查询是将一个查询语句嵌套在另一个查询语句中的技术。根据提供的引用内容，以下是一个示例Hive查询，其中包含了字段嵌套子查询：

SET sdate=2019-07-15; --上周一
SET edate=2019-07-21; -- 上周日
SET company=anlle;
SET recomrecords=ods.ods_${hiveconf:company}_KPI_RecomRecords;
SET employees=ods.ods_${hiveconf:company}_base_employees;
SET branchs=ods.ods_${hiveconf:company}_base_branchs;
SET regions=ods.ods_${hiveconf:company}_base_regions;

SELECT
CASE WHEN d.Name IS NOT NULL THEN d.Name ELSE '' END AS regionName,
CASE WHEN c.Name IS NOT NULL THEN c.Name ELSE '' END AS branchName,
CASE WHEN A.WorkNo IS NOT NULL THEN A.WorkNo ELSE '' END AS workNo,
CASE WHEN A.Fullname IS NOT NULL THEN A.Fullname ELSE '' END AS employeeName,
CASE WHEN A.EntryDate IS NOT NULL THEN A.EntryDate ELSE '' END AS entryDate
FROM
${hiveconf:employees} A
LEFT JOIN
${hiveconf:branchs} c ON A.BranchNo=c.Number
LEFT JOIN
${hiveconf:regions} d ON concat(concat('%{',A.BranchNo),'}%')
WHERE
RankSery LIKE 'C%'
AND IsLeave IS NULL
AND branchno NOT LIKE '%lni'
AND branchno NOT LIKE '%yunying'
AND NOT EXISTS (
SELECT 1
FROM ${hiveconf:recomrecords} B
WHERE A.Number=B.EmployeeNo
AND PhaseId=10
AND FillinDate<='2019-07-20 00:00:00'
AND FillinDate<='2019-07-21 23:59:59'
AND IsApprove=1
AND IsCancel IS NULL
)

在这个查询中，嵌套子查询被用作左连接的条件和WHERE子句的一部分。嵌套子查询的结果将用于过滤和匹配主查询中的数据。通过使用嵌套子查询，可以根据特定的条件来选择和处理字段的值。
#### 引用[.reference_title]
- *1* [使用嵌套子查询优化hive多连接查询](https://blog.csdn.net/m0_37773338/article/details/87746538)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down28v1,239^v3^insert_chatgpt"}} ] [.reference_item]
- *2* *3* [hive：语法：怎样写exist/in以及not exists/not in子句 和 字段嵌套子查询](https://blog.csdn.net/weixin_38750084/article/details/97773323)[target="_blank" data-report-click={"spm":"1018.2226.3001.9630","extra":{"utm_source":"vip_chatgpt_common_search_pc_result","utm_medium":"distribute.pc_search_result.none-task-cask-2~all~insert_cask~default-1-null.142^v91^insert_down28v1,239^v3^insert_chatgpt"}} ] [.reference_item]
[ .reference_list ]