MySQL 多次连接到付款数据的日历表

Question

我有一个如下所示的订单表

order_id  pre_pay_time    pre_pay_amount    pre_pay_type    final_payment_time    final_payment_amount    final_payment_type   
==============================================================================================================================
1           1234123413           10           1               1234123913               25                    2     
2           1234123414           25           1               0                        100                   0       
3           1234123417           75           2               1234125416               155                   1        
4           0                     0           0               1234126418               60                    2        

在这里，客户可以对订单进行预付款，然后在最后支付剩余款项，或者他们可以在最后支付全额。

pre_pay_time 和 final_payment_time 列是 UNIX 时间戳。

我想要做的是生成一个输出表，其中包含每个日历日的总金额。为此，我加入了一个日历表。

目前我只能成功输出最终付款金额的数据，以及每月每一天的现金和卡付款金额(基于 final_payment_type 列)。

SELECT calendar.datefield AS DATE, IFNULL( SUM( orders.final_payment_amount ) , 0 ) AS total_sales,
IFNULL(sum(if(final_payment_type=1,orders.final_payment_amount,0)),0)AS total_cash,
IFNULL(sum(if(final_payment_type=2,orders.final_payment_amount,0)),0)AS total_card,
count(orders.id) AS order_counter
FROM orders
RIGHT JOIN calendar ON ( DATE( FROM_UNIXTIME( cast(orders.final_payment_time as signed) ) ) = calendar.datefield ) 
WHERE calendar.datefield >= '2016-4-1' AND calendar.datefield <= '2016-4-31'
GROUP BY DATE

我希望做的是扩展查询，这样我也可以根据 pre_pay_time 获得 pre_pay_amount 每天的总和值。这将使我能够将当天的总收入计算为 final_payment_amount 和 pre_pay_amount 的组合。

由于预付款可能与最终付款在不同的一天进行，我相信我将不得不使用 pre_pay_time 列对同一个日历表进行另一个 JOIN。

这可能与一个查询有关吗？

Answer 1

你可以做这样的事情......你为你的预付款查询和最终付款查询加上别名，然后通过日期字段加入它们。

 SELECT *
  FROM (  SELECT calendar.datefield AS FinalDate,
                 IFNULL (SUM (orders.final_payment_amount), 0)
                    AS total_final_sales,
                 IFNULL (
                    SUM (
                       if (final_payment_type = 1,
                           orders.final_payment_amount,
                           0)),
                    0)
                    AS total_final_cash,
                 IFNULL (
                    SUM (
                       if (final_payment_type = 2,
                           orders.final_payment_amount,
                           0)),
                    0)
                    AS total_final_card,
                 COUNT (orders.id) AS order_final_counter
            FROM orders
                 RIGHT JOIN calendar
                    ON (DATE (
                           FROM_UNIXTIME (
                              CAST(orders.final_payment_time AS signed))) =
                           calendar.datefield)
           WHERE     calendar.datefield >= '2016-4-1'
                 AND calendar.datefield <= '2016-4-31'
        GROUP BY FinalDate) finalPay,
       (  SELECT calendar.datefield AS PreDate,
                 IFNULL (SUM (orders.pre_payment_amount), 0) AS total_pre_sales,
                 IFNULL (
                    SUM (
                       if (pre_payment_type = 1, orders.pre_payment_amount, 0)),
                    0)
                    AS total_pre_cash,
                 IFNULL (
                    SUM (
                       if (pre_payment_type = 2, orders.pre_payment_amount, 0)),
                    0)
                    AS total_pre_card,
                 COUNT (orders.id) AS order_pre_counter
            FROM orders
                 RIGHT JOIN calendar
                    ON (DATE (
                           FROM_UNIXTIME (
                              CAST(orders.pre_payment_time AS signed))) =
                           calendar.datefield)
           WHERE     calendar.datefield >= '2016-4-1'
                 AND calendar.datefield <= '2016-4-31'
        GROUP BY PreDate) prePay
 WHERE prePay.PreDate = finalPay.FinalDate