我只想从我的INNER JOIN查询中拉入1个匹配

Question

我正在使用其他表上的多个INNER JOIN查询构建一个表，其中一个查询返回多个匹配(正确地基于此函数的工作方式)，但我真的只需要回拉一个匹配，它在比较中将首先命中。下面是我的代码。在最后一个INNER JOIN查询中，在repldoadm中可以有几个a.transaction_id = e.p pb_trans_id匹配。Vw_ire_trade_transactions表。我能指定只带回一根火柴吗?

"

SELECT
distinct a.transaction_id,
b.currency_id,
h.SIG_CURRENCY_CODE,
b.charge_id,
c.charge_type_id,
d.charge_group,
e.account_name,
f.sig_entity_label,
e.trading_userid,
e.exchange_id,
e.tradeable_instr_name,
g.underlying_sym_bloomberg,
g.fut_expiration_date,
e.trade_date,
timestamp_table.timeformated,
a.rate * a.basis_value AS dma_fee,
SUM(A.RATE * A.BASIS_VALUE) OVER () as DMA_FEE_TOTAL
FROM
repldoadm.ire_estimate_trans_map       a
LEFT JOIN repldoadm.ire_charges_estimate         b ON a.estimate_id = b.estimate_id
LEFT JOIN repldoadm.ire_charges_lu               c ON b.charge_id = c.charge_id
LEFT JOIN repldoadm.ire_charge_types_lu          d ON c.charge_type_id = d.charge_type_id
LEFT JOIN repldoadm.vw_ire_trade_transactions    e ON a.transaction_id = e.pb_trans_id --or a.transaction_id = e.PB_TRANS_ID
LEFT JOIN (
SELECT
to_char(create_ts, 'HH24:MI:SS') AS timeformated,
trans_id,
pb_trans_id
FROM
repldoadm.vw_ire_trade_transactions
)                                      timestamp_table ON ( a.transaction_id = timestamp_table.trans_id or a.transaction_id = timestamp_table.pb_trans_id)
LEFT JOIN repldoadm.vw_ire_accounts              f ON e.account_name = f.pb_account_name
LEFT JOIN stig_adm.instrument_universe           g ON e.tradeable_instr_name = g.short_name
LEFT JOIN STIG_ADM.BBG_LU_CURRENCY h                ON c.payment_currency_id = h.SIG_CURRENCY_ID

"

Answer 1

可以使用带有limit子句的横向连接。对于横向外连接，我建议使用OUTER APPLY，因为与LEFT JOIN LATERAL不同，您不必添加虚拟ON子句来获得此语法正确。

SELECT
distinct a.transaction_id,
b.currency_id,
h.SIG_CURRENCY_CODE,
b.charge_id,
c.charge_type_id,
d.charge_group,
e.account_name,
f.sig_entity_label,
e.trading_userid,
e.exchange_id,
e.tradeable_instr_name,
to_char(e.create_ts, 'HH24:MI:SS'),
g.underlying_sym_bloomberg,
g.fut_expiration_date,
e.trade_date,
--    timestamp_table.timeformated,
a.rate * a.basis_value AS dma_fee,
SUM(A.RATE * A.BASIS_VALUE) OVER () as DMA_FEE_TOTAL
FROM
repldoadm.ire_estimate_trans_map       a
LEFT JOIN repldoadm.ire_charges_estimate         b ON a.estimate_id = b.estimate_id
LEFT JOIN repldoadm.ire_charges_lu               c ON b.charge_id = c.charge_id
LEFT JOIN repldoadm.ire_charge_types_lu          d ON c.charge_type_id = d.charge_type_id
OUTER APPLY
(
SELECT *
FROM repldoadm.vw_ire_trade_transactions tt
WHERE tt.pb_trans_id = a.transaction_id
FETCH FIRST ROW ONLY
) e;

Answer 2

如果您不关心返回哪一行，也许ROW_NUMBER分析函数可能会有所帮助。您当前的查询—添加了命名函数—将被用作子查询(或CTE，如我的示例)，而"最后"查询将返回RN = 1.

像这样:

WITH temp AS
(  SELECT DISTINCT a.transaction_id,
b.currency_id,
h.SIG_CURRENCY_CODE,
b.charge_id,
c.charge_type_id,
d.charge_group,
e.account_name,
f.sig_entity_label,
e.trading_userid,
e.exchange_id,
e.tradeable_instr_name,
TO_CHAR (e.create_ts, 'HH24:MI:SS'),
g.underlying_sym_bloomberg,
g.fut_expiration_date,
e.trade_date,
a.rate * a.basis_value AS dma_fee,
SUM (A.RATE * A.BASIS_VALUE) OVER () AS DMA_FEE_TOTAL,
-- this:
ROW_NUMBER () OVER (PARTITION BY e.pb_trans_id ORDER BY e.create_ts DESC) rn
FROM repldoadm.ire_estimate_trans_map a
LEFT JOIN repldoadm.ire_charges_estimate b
ON a.estimate_id = b.estimate_id
LEFT JOIN repldoadm.ire_charges_lu c
ON b.charge_id = c.charge_id
LEFT JOIN repldoadm.ire_charge_types_lu d
ON c.charge_type_id = d.charge_type_id
LEFT JOIN repldoadm.vw_ire_trade_transactions e
ON a.transaction_id = e.pb_trans_id
)
SELECT t.*
FROM temp t
WHERE t.rn = 1