如何合并选择查询结果集中的行

Question

我的结果是这样的

date      acc     cr      date       acc     dr
---------------------------------------------------
null      null    0       13/3/12    A       1300
null      null    0       13/3/12    c       1200 
null      null    0       13/3/12    D       1100
13/3/12   A       1000    null       null    0
18/3/12   E       2000    null       null    0
19/3/12   F       3000    null       null    0
31/3/12   G       3000    null       null    0

这个结果来自于以下查询，通过连接两个表获得现金账簿

select(case when mli.voucher_type = 1 THEN tav.voucher_date end)pdate,
(case when mli.voucher_type = 1 THEN mli.description end) acc,
(case when tav.voucher_type_id = 1 then sum(tvl.amount) else 0 end) cr,
(case when mli.voucher_type = 2 THEN tav.voucher_dateend) rdate,
(case when mli.voucher_type = 2 THEN mli.description end) acc,
(case when tav.voucher_type_id = 2 then sum(tvl.amount) else 0 end) dr
from t_acc_voucher tav
join t_voucher_ledger tvl on tvl.voucher_id = tav.voucher_id
join m_ledger_index mli on mli.ledger_index_id = tvl.ledger_index_id
group by mli.description, mli.voucher_type, tav.voucher_type_id,tav.voucher_date

我想要这样的结果

date      acc     cr      date       acc     dr
---------------------------------------------------
13/3/12   A       1000    13/3/12    A       1300
18/3/12   E       2000    13/3/12    c       1200
19/3/12   F       3000    13/3/12    D       1100
31/3/12   G       3000    null       null    0

有没有人能帮到me.or，给出一些建议，是用写的方式得到它，还是我可以用2种不同的查询来尝试。提前感谢

Answer 1

将您的查询分解为两个独立的查询，一个用于赊账，另一个用于借方，并根据降序日期执行一个完整的外部连接，您实际上可以按任何列订购。从最初的查询中，我假设VOUCHER_TYPE =1是信用，VOUCHER_TYPE =2是借方。

试试这个(没有测试)

with CREDITS as (select TAV.VOUCHER_DATE PDATE,
                        MLI.DESCRIPTION ACC,
                        (case when TAV.VOUCHER_TYPE_ID = 1 then SUM(TVL.AMOUNT) else 0 end) CR
                   from t_acc_voucher tav
                   join t_voucher_ledger tvl 
                        on tvl.voucher_id = tav.voucher_id
                   join M_LEDGER_INDEX MLI 
                        on MLI.LEDGER_INDEX_ID = TVL.LEDGER_INDEX_ID
                  where MLI.VOUCHER_TYPE = 1
                  group by MLI.DESCRIPTION, 
                           MLI.VOUCHER_TYPE, 
                           TAV.VOUCHER_TYPE_ID,
                           TAV.VOUCHER_DATE),
      debits as (select TAV.VOUCHER_DATE RDATE,
                        MLI.DESCRIPTION ACC,
                        (case when TAV.VOUCHER_TYPE_ID = 2 then SUM(TVL.AMOUNT) else 0 end) DR
                   from T_ACC_VOUCHER TAV
                  where mli.voucher_type = 2
                   join t_voucher_ledger tvl 
                        on tvl.voucher_id = tav.voucher_id
                   join M_LEDGER_INDEX MLI 
                        on MLI.LEDGER_INDEX_ID = TVL.LEDGER_INDEX_ID
                  group by MLI.DESCRIPTION, 
                           MLI.VOUCHER_TYPE, 
                           TAV.VOUCHER_TYPE_ID,
                           TAV.VOUCHER_DATE  )
select T1.PDATE, T1.ACC, T1.CR, T2.RDATE, T2.ACC, T2.DR
  from (select a.*, row_number() over (order by a.PDATE) RN
          from credits a) T1
  full outer join 
        select b.*, row_number() over (order by b.RDATE) RN
          from debits b) T2
       on (t1.rn = t2.rn);

Answer 2

select t1.date1, t1.acc1, t1.cr, t2.date2, t2.acc2, t2.dr
from (the table or query) t1 
    join (the table or query) t2 on t1.acc1=t2.acc2
where t1.acc1 is not null

我们通过acc两次将同一个查询(或表)连接到它自己。