sql - Oracle 查询未返回来自两个子查询的所有行
问题描述
我有两个子查询,它们本质上是在查询相同的东西减去一个维度(标志列)。然后我从这两个子查询中获取数据并在“外部查询”中返回它们。(原始值和 RE 值 (VAL) 在同一行中)
我加入的复合键由以下列组成:
P_NAME, REAL_DATE, UTC_TIME, DATA_TYPE & LOCAL_TS
从第一个子查询返回的一些示例数据
P_NAME REAL_DATE UTC_TIME DATA_TYPE VAL LOCAL_TS FLAG
John 01-01-2018 01:00:00AM Height 60 04:00:00AM ORIG
Jacob 01-01-2018 02:00:00AM Height 68 05:00:00AM ORIG
Jingle 01-01-2018 01:00:00AM Height 65 04:00:00AM ORIG
Heimer 01-01-2018 04:00:00AM Height 68 07:00:00AM ORIG
Schmidt 01-01-2018 05:00:00AM Height 72 08:00:00AM ORIG
从第二个子查询返回的一些示例数据
P_NAME REAL_DATE UTC_TIME DATA_TYPE VAL LOCAL_TS FLAG
John 01-01-2018 01:00:00AM Height 90 04:00:00AM RE
Jacob 01-01-2018 02:00:00AM Height 98 05:00:00AM RE
Jingle 01-01-2018 01:00:00AM Height 95 04:00:00AM RE
我需要从第一个和第二个子查询返回
P_NAME REAL_DATE UTC_TIME DATA_TYPE O_VAL RE_VAL LOCAL_TS
John 01-01-2018 01:00:00AM Height 60 90 04:00:00AM
Jacob 01-01-2018 02:00:00AM Height 68 98 05:00:00AM
Jingle 01-01-2018 01:00:00AM Height 65 95 04:00:00AM
Heimer 01-01-2018 04:00:00AM Height 68 null 07:00:00AM
Schmidt 01-01-2018 05:00:00AM Height 72 null 08:00:00AM
我尝试过但不起作用的方法:
select O.O_P_NAME as P_NAME
, O.O_REAL_DATE as REAL_DATE
, O.O_UTC_TIME as UTC_TIME
, O.O_DATA_TYPE as DATA_TYPE
, CASE WHEN O.O_VAL = R.R_VAL
THEN null
ELSE R.R_VAL
END as RESUBMITTED_VAL
, O.O_VAL as ORIG_VAL
, O.O_LOCAL_TS as LOCAL_TS
FROM(
(SELECT P_NAME as O_P_NAME
, REAL_DATE as O_REAL_DATE
, UTC_TIME as O_UTC_TIME
, DATA_TYPE as O_DATA_TYPE
, VAL as O_VAL
, LOCAL_TS as O_LOCAL_TS
, FLAG as O_FLAG
FROM TABLE
WHERE FLAG = 'ORIG'
) O
JOIN
(SELECT P_NAME as R_P_NAME
, REAL_DATE as R_REAL_DATE
, UTC_TIME as R_UTC_TIME
, DATA_TYPE as R_DATA_TYPE
, VAL as R_VAL
, LOCAL_TS as R_LOCAL_TS
, FLAG as R_FLAG
FROM TABLE
WHERE FLAG = 'RE'
) R
ON O.O_P_NAME = R.R_P_NAME
AND O.O_REAL_DATE = R.R_REAL_DATE
AND O.O_UTC_TIME = R.R_UTC_TIME
AND O.O_DATA_TYPE = R.R_DATA_TYPE
AND O.O_LOCAL_TS = R.R_LOCAL_TS
);
解决方案
您不需要这两个子查询。只需做一个 case 语句来分隔字段。
SELECT P_NAME
, REAL_DATE
, UTC_TIME
, DATA_TYPE
, MAX(CASE WHEN FLAG='ORIG' THEN VAL END) as O_VAL
, MAX(CASE WHEN FLAG='RE' THEN VAL END) as R_VAL
, LOCAL_TS
FROM TABLE
GROUP BY P_NAME, REAL_DATE, UTC_TIME, DATA_TYPE, LOCAL_TS
奖励答案:如果您真的决定需要子查询,请使用 UNION 而不是连接,因为您不知道哪个表会有额外的值。
推荐阅读
- javascript - 是否可以通过“谷歌浏览器”中的 url/js/php 执行程序?
- python - 如何使用 Python 在 csv 上写入变量
- c++ - 用一个参数计算循环链表中的节点?
- javascript - 从输入中提取一个值并将其存储在数组中
- mongodb - 在NestJS中做种子mongoDB的正确方法是什么,使用猫鼬并利用我已经定义的schmas
- android - RecyclerView 不显示元素
- c# - 如何用c#逐行读取docx文件?
- javascript - 如何将 Firebaseui 身份验证与 Redux 集成
- c# - 如何为 NHibertnateSession.cs 添加第二个映射 xml?
- architecture - 一个生产者和几个消费者从证券交易所获取市场数据的架构