oracle 将返回结果列变行

阿拉狗

于 2024-07-15 17:41:06 发布

阅读量193

点赞数 1

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_58691039/article/details/140444771

版权


SELECT sum(a.amount) * 1.15 as total_amount_adjusted
FROM ac_payment_202407 a
WHERE a.acct_cycle = TO_CHAR(SYSDATE, 'yyyymm') 
AND a.balance_type = '8046403'

UNION ALL

SELECT sum(a1.begin_balance) as total_begin_balance
FROM AC_BALANCE_DEPOSIT a1
WHERE a1.acct_id = '31100001344249'
AND a1.balance_type = '8041801';

正常返回结果；

使用 dual 表，这是 Oracle 中的一个特殊虚拟表，用于从中选择一行数据而不需要实际表。每个子查询分别计算总和，并在单行中作为列显示结果。

SELECT 
    (SELECT SUM(a1.begin_balance)
     FROM AC_BALANCE_DEPOSIT a1
     WHERE a1.acct_id = '31100001344249'
     AND a1.balance_type = '8041801') AS begin_balance,
    (SELECT SUM(a.amount) * 1.15 
     FROM ac_payment_202407 a
     WHERE a.acct_cycle = TO_CHAR(SYSDATE, 'yyyymm')
     and a.acct_id = '31100001344249'
     AND a.balance_type = '8046403') AS amount
FROM dual

结果：