sql - 连接多个查询的输出
问题描述
我一直在尝试加入两个具有共同字段的查询的输出,我已经查看了人们之前在类似问题和答案中提供的几个示例,但似乎无法让它们为我工作。
我有两个查询,基本上提供Account_ID, Specific_Transaction_Type_Amount, Transaction_Date作为第一个查询的输出,然后第二个查询输出Account_ID, Account_Value。
第二个查询有点复杂,因为它必须查找帐户商品 ID、该商品的最近价格日期、该价格日期的价格,然后将该商品的数量相加,乘以该商品的分组价格按帐户 ID。
所以第一个查询看起来有点像这样:
Select distinct
ACCOUNT_NUMBER
,TrANSACTION_AMOUNT
,TRANSACTION_DATE
from CLIENT CL
left join
CLIENT_ACCOUNT_LINK CAL on CL.CLIENT_ID = CAL.CAL_CLIENT_ID
left join
ACCOUNT A on CAL.CAL_ACCOUNT_ID = A.ACCOUNT_ID
left join
ACCOUNT_TYPE AT on A.ACCOUNT_TYPE_ID = AT.ACCOUNT_TYPE_ID
left join
ACCOUNT_TRANSACTION ATR on A.ACCOUNT_ID = ATR.ACCOUNT_ID
WHERE
AT.ACCOUNT_TYPE = 'ACCOUNTYPE2'
AND ATR.TRANSACTION_TYPE = 'TRANSACTIONTYPE6'
第二个查询如下所示:
select distinct
A.ACCOUNT_NUMBER
,SUM(AB.ITEM_QTY * PR.ITEM_PRICE) OVER (PARTITION BY A.ACCOUNT_ID) as TotalValue
from ACCOUNT A
join ACCOUNT_BALANCE AB on A.ACCOUNT_ID = AB.ACCOUNT_ID
join ITEM_LIST IL on AB.ITEM_ID = IL.ITEM_ID
join PRICE_DATE PD on IL.ITEM_ID= PD.ITEM_ID
join CODE_LOOKUP cODE on PD.PRICE_STATUS_CODE = CODE.CODE_ID AND CODE.DESCRIPTION = 'ACTIVE'
AND PD.PRICE_DATE = (SELECT MAX(PD2.PRICE_DATE)
FROM PRICE_DATE PD2
WHERE PD2.ITEM_ID = PD.ITEM_ID
AND PD2.PRICE_STATUS_CODE = CODE.CODE_ID
AND sprcp.prcp_effective_date < sysdate + 1)
join PRICE PR on PD.PRICE_ID = PRICE_PRICE_DATE_ID
join PRICE_TYPE PT on PR.PRICE_TYPE_ID = PT.PRICE_TYPE_ID
所以我得到两个结果:
ACCOUNT_ID TRANSACTION_AMOUNT TRANSACTION_DATE
_____________________________________________________
123456789 500.23 07/02/2019
ACCOUNT_ID TOTAL_VALUE
____________________________
123456789 24365.48
我希望TOTAL_VALUE
查询 2 中的列出现在查询 1 中。如果我将两个查询合并为一个,查询会运行很长时间,然后才会出现内存不足错误。如果我分别运行它们,它们每个都需要几分钟,但我得到的结果没有任何问题。
我确实根据之前的示例进行了尝试,例如:
SELECT *
FROM (QUERY1) AS Q1
LEFT JOIN (QUERY2) AS Q2
on Q1.A.ACCOUNT_ID = Q2.A.ACCOUNT_ID
尝试运行此查询时出现语法错误,它似乎特别不喜欢查询别名?
解决方案
尝试
with
q2 (select distinct
A.ACCOUNT_NUMBER
,SUM(AB.ITEM_QTY * PR.ITEM_PRICE) OVER (PARTITION BY A.ACCOUNT_ID) as TotalValue
from ACCOUNT A
join ACCOUNT_BALANCE AB on A.ACCOUNT_ID = AB.ACCOUNT_ID
join ITEM_LIST IL on AB.ITEM_ID = IL.ITEM_ID
join PRICE_DATE PD on IL.ITEM_ID= PD.ITEM_ID
join CODE_LOOKUP cODE on PD.PRICE_STATUS_CODE = CODE.CODE_ID AND CODE.DESCRIPTION = 'ACTIVE'
AND PD.PRICE_DATE = (SELECT MAX(PD2.PRICE_DATE)
FROM PRICE_DATE PD2
WHERE PD2.ITEM_ID = PD.ITEM_ID
AND PD2.PRICE_STATUS_CODE = CODE.CODE_ID
AND sprcp.prcp_effective_date < sysdate + 1)
join PRICE PR on PD.PRICE_ID = PRICE_PRICE_DATE_ID
join PRICE_TYPE PT on PR.PRICE_TYPE_ID = PT.PRICE_TYPE_ID),
q1 as (Select distinct
ACCOUNT_NUMBER
,TrANSACTION_AMOUNT
,TRANSACTION_DATE
from CLIENT CL
left join
CLIENT_ACCOUNT_LINK CAL on CL.CLIENT_ID = CAL.CAL_CLIENT_ID
left join
ACCOUNT A on CAL.CAL_ACCOUNT_ID = A.ACCOUNT_ID
left join
ACCOUNT_TYPE AT on A.ACCOUNT_TYPE_ID = AT.ACCOUNT_TYPE_ID
left join
ACCOUNT_TRANSACTION ATR on A.ACCOUNT_ID = ATR.ACCOUNT_ID
WHERE
AT.ACCOUNT_TYPE = 'ACCOUNTYPE2'
AND ATR.TRANSACTION_TYPE = 'TRANSACTIONTYPE6')
select * from q1
join q2 on q1.account_number = q2.account_number
推荐阅读
- python - AttributeError:LstmNetwork 实例没有属性“x_list_add”
- django - 从 Django 数据库模型中获取项目
- react-native - 如何在 react native 中将状态变量设置为组件的属性?
- android - 如何为android和ios的appium自动化运行单一代码结构?
- javascript - After passing the value from JavaScript to C#, how to do some automatic operation with it?
- android - 张量流 LSTM 不起作用
- angular - Nativescript procudes bootstrap: StaticInjectorError(AppModule)[EffectSources -> ErrorHandler]:
- sed - 如何在不使用 sed 在保持空间中插入新行的情况下从模式空间追加到保持空间?
- asp.net-mvc - 选择不按它们被选择的顺序出现 - Select2 js
- node.js - index.ejs 与 index.html