时间戳

首页 > 解决方案 > 如何从pl sql中的两个表中选择具有相同日期的行

oracle - 如何从pl sql中的两个表中选择具有相同日期的行

问题描述

我有两个带有公共列“hist_date”的表。我只想要具有共同日期的行。

客户表

+-----------+--------+-----------+
| hist_date | payable| receivable|
+-----------+--------+-----------+
|03-Jan-2021| 1200.00|       0.00|
|04-Jan-2021| 1300.00|       0.00|
|05-Jan-2021| 2500.00|       0.00|
|06-Jan-2021|    0.00|    1653.00|
|07-Jan-2021|    0.00|    1452.34|
+-----------+--------+-----------+

GL_table

+-----------+-----------+--------------+
| hist_date | gl_payable| gl_receivable|
+-----------+-----------+--------------+
|03-Jan-2021|    1200.00|          0.00|
|04-Jan-2021|    1300.00|          0.00|
|06-Jan-2021|       0.00|       1653.00|
|07-Jan-2021|       0.00|       1452.34|
+-----------+-----------+--------------+

现在我希望结果是

+-----------+-----------+--------------+--------+-----------+
| hist_date | gl_payable| gl_receivable| payable| receivable|
+-----------+-----------+--------------+--------+-----------+
|03-Jan-2021|    1200.00|          0.00| 1200.00|       0.00|
|04-Jan-2021|    1300.00|          0.00| 1300.00|       0.00|
|06-Jan-2021|       0.00|       1653.00|    0.00|    1653.00|
|07-Jan-2021|       0.00|       1452.34|    0.00|    1452.34|
+-----------+-----------+--------------+--------+-----------+

谁能提供正确的查询？

编辑：

很抱歉，我应该提到结果表已经从一个复杂的查询中派生出来。

SELECT A.hist_date,SUM(A.payable),SUM(A.gl_payable),SUM(A.payable)-SUM(A.gl_payable) payable_diff,SUM(A.receivable),SUM(A.gl_recievable),SUM(A.receivable)-SUM(A.gl_recievable) receivable_diff
FROM (
    select h.hist_date,SUM(h.current_balance) payable,0 gl_payable,0 receivable,0 gl_recievable
    from investor_financials_hist h
    inner join investor_accounts ia on ia.account_id=h.account_id
    where ia.product_id in (1,2)
    and ia.investor_code not like 'F%'
    and ia.investor_code not like 'A%'
    and h.current_balance>0
    group by h.hist_date
    UNION ALL
    select h.hist_date,0 payable,0 gl_payable,SUM(h.current_balance) receivable,0 gl_recievable
    from investor_financials_hist h
    inner join investor_accounts ia on ia.account_id=h.account_id
    where ia.product_id in (1,2)
    and ia.investor_code not like 'F%'
    and ia.investor_code not like 'A%'
    and h.current_balance<0
    group by h.hist_date
    UNION ALL
    select g.trans_dt hist_date,0 payable,0 gl_payable,0 receivable,PKG_USL.GET_GL_OPENING_BALANCE(g.ac_cat,g.ac_no,g.trans_dt)+SUM(decode(g.dr_cr,'DR',g.amount_ccy))-SUM(decode(g.dr_cr,'CR',g.amount_ccy)) gl_recievable
    from gl_trans_hist g
    where g.ac_no='0102090101000'
    group by g.trans_dt,g.ac_cat,g.ac_no
    UNION ALL
    select g.trans_dt hist_date,0 payable,PKG_USL.GET_GL_OPENING_BALANCE(g.ac_cat,g.ac_no,g.trans_dt)+SUM(decode(g.dr_cr,'CR',g.amount_ccy))-SUM(decode(g.dr_cr,'DR',g.amount_ccy)) gl_payable,0 receivable,0 gl_recievable
    from gl_trans_hist g
    where g.ac_no='0201060100000'
    group by g.trans_dt,g.ac_cat,g.ac_no
)A
GROUP BY A.hist_date
ORDER BY A.hist_date

标签： oracleplsqloracle11g

解决方案

看起来像一个简单的连接，根本没有任何技巧。

  SELECT c.hist_date,
         g.gl_payable,
         g.gl_receivable,
         c.payable,
         c.receivable
    FROM client_table c JOIN gl_table g ON g.hist_date = c.hist_date
ORDER BY c.hist_date

推荐阅读