时间戳

首页 > 解决方案 > 使用联合后如何拆分列

sql - 使用联合后如何拆分列

问题描述

select *
  from (select debit
          from tblarpayments a
         where     paymenttype = 6
               and credit = 0
               and checkid = 522
        union
        select credit
          from tblarpayments b
         where     paymenttype = 6
               and debit = 0
               and checkid = 522
        union
        select debit
          from tblarpayments c
         where     ref# like 'A/R%'
               and checkid = 522
        union
        select credit
          from tblarpayments d
         where     debit = 0
               and paymenttype = 2
               and checkid = 522
        union
        select debit
          from tblarpayments e
         where     credit = 0
               and ref# not like 'A/R%'
               and checkid = 522)
| |   DEBIT   |
|1| 10.0000   |
|2| 240.0000  |
|3| 250.0000  |
|4| 11540.0000|

我希望它在 1 行这样

 | |DEBIT     |CREDIT|DEBIT1 |CREDIT1 |DEBIT2  |
 |1|11540.0000|      |10.0000|250.0000|240.0000|

第二个单元格中的损失收益佣金贷方借方贷方没有价值，因为checkid = 522借方= 0支付类型= 6没有贷方值，它为空。全部在 1 行中，我该如何解决？我尝试了很多东西，但我无法弄清楚。

标签： sqloracleoracle11g

解决方案

假设您的每一行返回不超过一行，那么这对您有用：

select t1.debit as LOSS
       , t2.credit as GAIN 
       , t3.debit as COMMISSION 
       , t4.credit as CREDIT 
       , t5.debit as DEBIT   
from ( 
    select *
    from tblarpayments a
    where  checkid = 522
    and paymenttype = 6
    and credit = 0
) t1
left outer join ( 
    select *
    from tblarpayments a
    where  checkid = 522
    and  paymenttype = 6
    and debit = 0
) t2 on t1.checkid = t2.checkid
left outer join ( 
    select *
    from tblarpayments a
    where  checkid = 522
    and ref# like 'A/R%'
) t3 on t1.checkid = t3.checkid
left  outer join ( 
    select *
    from tblarpayments a
    where  checkid = 522
    and debit = 0
    and paymenttype = 2
) t4 on t1.checkid = t4.checkid
left outer join ( 
    select *
    from tblarpayments a
    where  checkid = 522
    and credit = 0
    and ref# not like 'A/R%'
) t5 on t1.checkid = t5.checkid

我使用 LEFT OUTER JOIN 的假设是您将始终拥有 LOSS 记录，但您可能没有所有记录（您的图像显示 GAIN 没有价值）。但是，FULL OUTER JOIN 可能更合适：您知道您的数据而我不知道。或者，如果您可以保证每个子查询中总会有一条记录，那么您应该改用 INNER JOIN。

请注意，如果任何子查询确实返回多于一行，您将获得产品，因此您必须相应地调整过滤器。

编写此查询可能有更多的执行方式。但是，如果没有实际的表结构和代表性数据，我将编写多少推测代码是有限度的。

推荐阅读