组合两个已经运行的 ORACLE 查询

Question

我想就以下两个问题寻求帮助。他们单独工作就像一个魅力，现在我希望他们一起工作。

它们都提供会员卡数据库。

第一个根据用户的唯一卡号（%1111%、%2222%、%3333%）获取与用户相关的各种信息。下面的示例）并返回提供卡号的按照自定义顺序的行。

SELECT cardnumber, first_name || ' ' || last_name 
FROM (
     SELECT cardnumber, first_name, last_name, c.OrderNo
     FROM ag_cardholder ch, (SELECT '%1111%' cardmask, 1 OrderNo from dual
                        UNION ALL
                        SELECT '%2222%', 2 OrderNo from dual
                        UNION ALL
                        SELECT '%3333%', 3 OrderNo from dual
                        ) c
     WHERE ch.cardnumber LIKE c.cardmask
     Order by c.OrderNo
) t

第二个代码根据卡号和类型返回卡的当前积分余额最后用于写入点的终端的在卡上。有两种类型的终端对实际余额的计算方式不同（其中一种需要余额列中的数字，另一种需要求和 余额和总积分列）。

SELECT *
FROM
(SELECT
cardnumber,
(SELECT last_name || ' ' || first_name FROM ag_cardholder WHERE cardnumber = '1111'),
CASE
WHEN terminal_id LIKE 'AGHUPR9%' THEN card_balance
WHEN terminal_id LIKE 'AGHUPR7%' THEN card_balance + total_points
END
FROM ag_tranzakcio
WHERE cardnumber = '1111'
ORDER BY tran_date DESC)
WHERE ROWNUM = 1

我想要是一个执行这两项任务的查询：按提供的顺序获取卡号，并按相同的顺序返回一些用户信息以及当前余额。我们将非常感谢您的帮助。

更新：不幸的是，我对 ORACLE 还不够熟悉，甚至无法想出一个反复试验的解决方案，所以我现在依赖于您的洞察力。

Answer 1

SELECT cardnumber, name, balance, OrderNo
FROM
(
    SELECT 
    ch.cardnumber cardnumber, 
    ch.first_name || ' ' || ch.last_name name, 
    CASE WHEN t.terminal_id LIKE 'AGHUPR9%' THEN t.card_balance 
         WHEN t.terminal_id LIKE 'AGHUPR7%' THEN t.card_balance + t.total_points 
    END balance,
    rank () over (partition by ch.cardnumber order by t.tran_date DESC) rank,
    c.OrderNo as OrderNo
    FROM ag_cardholder ch
    JOIN (SELECT '%1111%' cardmask, 1 OrderNo from dual
                                UNION ALL
                                SELECT '%2222%', 2 OrderNo from dual
                                UNION ALL
                                SELECT '%3333%', 3 OrderNo from dual
                                ) c ON ch.cardnumber like c.cardmask
    JOIN ag_tranzakcio t ON ch.cardnumber = t.cardnumber 
)
WHERE rank = 1
ORDER BY OrderNo

编辑：假设 ag_tranzakcio.id 字段是一个可靠的递增键，这可以工作：

SELECT cardnumber, name, balance, OrderNo
FROM
(
    SELECT 
    ch.cardnumber cardnumber, 
    ch.first_name || ' ' || ch.last_name name, 
    CASE WHEN t.terminal_id LIKE 'AGHUPR9%' THEN t.card_balance 
         WHEN t.terminal_id LIKE 'AGHUPR7%' THEN t.card_balance + t.total_points 
    END balance,
    rank () over (partition by ch.cardnumber order by t.id DESC) rank,
    c.OrderNo as OrderNo
    FROM ag_cardholder ch
    JOIN (SELECT '%1111%' cardmask, 1 OrderNo from dual
                                UNION ALL
                                SELECT '%2222%', 2 OrderNo from dual
                                UNION ALL
                                SELECT '%3333%', 3 OrderNo from dual
                                ) c ON ch.cardnumber like c.cardmask
    JOIN ag_tranzakcio t ON ch.cardnumber = t.cardnumber 
)
WHERE rank = 1
ORDER BY OrderNo

Answer 2

修改后的要求是仅返回每张卡的最近交易。这可以通过使用分析函数 RANK() 对交易进行排序，然后应用包装查询来过滤计算出的排名来完成。

select cardnumber
       , card_holder
       , txn_amount
from 
    (
    SELECT ch.cardnumber
           , ch.first_name || ' ' || ch.last_name  as card_holder
           , CASE
                WHEN txn.terminal_id LIKE 'AGHUPR9%' THEN txn.card_balance
                WHEN txn.terminal_id LIKE 'AGHUPR7%' THEN txn.card_balance + txn.total_points
            END as txn_amount
           , rank () over (partition by ch.cardnumber order by txn.tran_date DESC) as rnk       
            , c.orderno
    FROM ag_cardholder ch
         join    (SELECT '%1111%' cardmask, 1 OrderNo from dual
                            UNION ALL
                            SELECT '%2222%', 2 OrderNo from dual
                            UNION ALL
                            SELECT '%3333%', 3 OrderNo from dual
                            ) c 
            on (ch.cardnumber LIKE c.cardmask)
         join ag_tranzakcio txn
            on (ch.cardnumber = txn.cardnumber)
    )        
where rnk=1       
order by orderno, cardnumber  
/