sql - 具有变量的普遍 SQL 子查询
问题描述
为了在不到几分钟的执行时间内获得我需要的数据,我有 2 个单独的查询:
select
' ' as POHI_ACD,
LTRIM(RTRIM(OePo_Header.PO_ID)) as POHI_PONUM,
' ' as POHI_POTYP,
' ' as POHI_VND,
LTRIM(RTRIM(OePo_Header.CLIENT_ID)) as POHI_CO,
'B1' as POHI_WA,
'n/a' as PODI_COMMNT, -- Comment filled in separate query
' ' as POHI_CUSER,
' ' as POHI_TSCRT,
' ' as POHI_PGMUP,
' ' as POHI_USRUP,
'01/01/0001 00:00:00' as POHI_TSLUP,
' ' as POHI_FILEPATH
from (
OePo_Header
inner join OePo_Item
on OePo_Header.PO_ID = OePo_Item.PO_ID
)
left join OePo_Remarks
on OePo_Header.WHSE_INST_ID = OePo_Remarks.AUTOKEY
and OePo_Header.PO_ID = OePo_Remarks.PO_ID
where (
(OePo_Item.UNITQTY_ORDER = 1)
and (OePo_Header.SHIPTO_ID like '%*B1')
and(OePo_Item.CLOSED_FLG = 'N')
and(OePo_Item.INVOICED_COUNT = 0)
and(OePo_Item.ITEM_TYPE = '2')
and(OePo_Item.VENDOR_ID <> 'WHSE')
)
group by
LTRIM(RTRIM(OePo_Header.PO_ID)),
LTRIM(RTRIM(OePo_Header.CLIENT_ID)),
'B1',
if(TEXT = Null, '', TEXT),
'',
'',
OEPO_HEADER.PO_ID
order by
OEPO_HEADER.PO_ID;
和
select
OePo_Remarks.TEXT as POHI_COMMNT
from (
OePo_Remarks
inner join OePo_Header
on OePo_Remarks.AUTOKEY = OePo_Header.WHSE_INST_ID
and OePo_Remarks.PO_ID = OePo_Header.PO_ID
)
where
OePo_Remarks.PO_ID like '%208672%' -- fill in with appropriate PO num from outer code, 208672 is just an example. (PO_IDs can be padded, hence the %s)
OePo_Remarks.zflags like '%w%'
我将如何设置一个子查询,它使用来自外部查询的数据来获取我正在寻找的内容,而不是组合调用这两个查询的代码中的数据?就像是:
select
' ' as POHI_ACD,
LTRIM(RTRIM(OePo_Header.PO_ID)) as POHI_PONUM,
' ' as POHI_POTYP,
' ' as POHI_VND,
LTRIM(RTRIM(OePo_Header.CLIENT_ID)) as POHI_CO,
'B1' as POHI_WA,
-- subquery start
( select
OePo_Remarks.TEXT
from (
OePo_Remarks
inner join OePo_Header
on OePo_Remarks.AUTOKEY = OePo_Header.WHSE_INST_ID
and OePo_Remarks.PO_ID = OePo_Header.PO_ID
)
where (
OePo_Remarks.PO_ID like CONCAT('%', POHI_PONUM, '%') -- Here is where I need the correct PONUM from the outer query.
and OePo_Remarks.zflags like '%w%'
)
) as POHI_COMMNT, --subquery end
' ' as POHI_CUSER,
' ' as POHI_TSCRT,
' ' as POHI_PGMUP,
' ' as POHI_USRUP,
'01/01/0001 00:00:00' as POHI_TSLUP,
' ' as POHI_FILEPATH
from (
OePo_Header
inner join OePo_Item
on OePo_Header.PO_ID = OePo_Item.PO_ID
)
left join OePo_Remarks
on OePo_Header.WHSE_INST_ID = OePo_Remarks.AUTOKEY
and OePo_Header.PO_ID = OePo_Remarks.PO_ID
where (
(OePo_Item.UNITQTY_ORDER = 1)
and (OePo_Header.SHIPTO_ID like '%*B1')
and(OePo_Item.CLOSED_FLG = 'N')
and(OePo_Item.INVOICED_COUNT = 0)
and(OePo_Item.ITEM_TYPE = '2')
and(OePo_Item.VENDOR_ID <> 'WHSE')
)
group by
LTRIM(RTRIM(OePo_Header.PO_ID)),
LTRIM(RTRIM(OePo_Header.CLIENT_ID)),
'B1',
if(OePo_Remarks.TEXT = Null, '', OePo_Remarks.TEXT),
'',
'',
OePo_Header.PO_ID
order by
OePo_Header.PO_ID;
使用效率低得多的单个查询,我可以在单个查询中获取所需的数据,但在此查询中执行需要几分钟,并且在寻找相似(但不相同)的单独查询中可能需要更长的数量级) 数据。有没有办法让这个工作与变量一起工作?(除非有一种join
组合可以让我在几分钟内收集到正确的数据?)
解决方案
您需要默认加载列值以使它们匹配,但我会尝试使用联合来使您的两个选择作为单个结果集出现。根据我的经验,找到两个更简单的查询组合在一个联合中比一个复杂的查询更快的情况并不少见。
推荐阅读
- javascript - 根据常数显示单选按钮
- python - 在 Flask 应用程序中绑定 LDAP 连接时出现 LDAPInvalidDNSyntaxResult 错误
- c# - 列表框中的素数应该出现在文本框中
- python - 通过 pytest 使用多进程处理时如何测量覆盖率?
- c# - MemoryStream 中的媒体未显示(或未播放)
- phaser-framework - 如何在Phaser 3中获取鼠标点与特定对象之间的距离
- python - MQTT & Postgresql DB:使用 python 将 mqtt 消息插入到 postgresql 表的列中
- django - Django Rest Framework:按用户名序列化用户多对多字段
- linux - 我怎样才能看到像这张照片一样的我的电脑规格?
- string - 从字符串中删除字符序列