求教一个sql语句

Question

两张表 ， 一个是订单，一个是付款， 想要分配付款金额和订单金额，根据先到先得的原理，例子
CREATE TABLE t1(
order_id varchar(2) primary key,
amount  int );CREATE TABLE t2(
payment_id varchar(2) primary key,
amount int );insert into t1 values('O1', 10);
insert into t1 values('O2', 20);
insert into t2 values('P1', 7);
insert into t2 values('P2', 40);谢谢

Answer 1

真的一个sql不能实现么不考虑数据量的情况下

Answer 2

WITH TA AS
 (SELECT A.ORDER_ID,
         O_AMOUNT,
         PAYMENT_ID,
         P_AMOUNT,
         CASE
           WHEN O_START <= P_START AND O_END >= P_END THEN
            P_AMOUNT
           WHEN O_START <= P_START AND O_END < P_END THEN
            O_END - P_START
           WHEN O_START >= P_START AND O_END >= P_END THEN
            P_END - O_START
           WHEN O_START >= P_START AND O_END <= P_END THEN
            O_AMOUNT
         END PAY
    FROM (SELECT ORDER_ID,
                 O_AMOUNT,
                 O_END,
                 O_START,
                 PAYMENT_ID,
                 P_AMOUNT,
                 P_START,
                 P_END
            FROM (SELECT ORDER_ID,
                         AMOUNT O_AMOUNT,
                         SUM(AMOUNT) OVER(ORDER BY ORDER_ID) O_END,
                         SUM(AMOUNT) OVER(ORDER BY ORDER_ID) - AMOUNT O_START
                  
                    FROM T1)
            FULL JOIN (SELECT PAYMENT_ID,
                             AMOUNT P_AMOUNT,
                             SUM(AMOUNT) OVER(ORDER BY PAYMENT_ID) P_END,
                             SUM(AMOUNT) OVER(ORDER BY PAYMENT_ID) - AMOUNT P_START
                        FROM T2)
              ON O_START < P_END
             AND O_END >= P_START) A
   ORDER BY ORDER_ID, PAYMENT_ID),
TB AS
 (SELECT NULL ORDER_ID, NULL, PAYMENT_ID, P_AMOUNT, P_AMOUNT - SUM(PAY)
    FROM TA
   GROUP BY PAYMENT_ID, P_AMOUNT
  HAVING P_AMOUNT > SUM(PAY)
   ORDER BY PAYMENT_ID)
SELECT * FROM TA
UNION ALL
SELECT * FROM TB