php - 如何通过mysql查询获取最近3个月的贷记借记总额？

Question

我需要过去三个月的贷方和借方总额。我现在所做的是获取贷方总额和借方总额，然后我用借方减去贷方总额，这是不正确的，请帮助我。

我需要单个用户过去三个月的(贷方 - 借方)。但现在它变成负值，这是不正确的。贷方和借方记录不应为负数(-ve)。积分每 3 个月到期

我的表格如下

CREATE TABLE `cb_customer_credit_log` (
  `id` int(11) NOT NULL,
  `ord_id` varchar(255) NOT NULL,
  `coupon_assignee` varchar(255) NOT NULL,
  `coupon_assigned_to_id` varchar(255) NOT NULL,
  `coupon_id` varchar(255) NOT NULL,
  `coupon_code` varchar(255) NOT NULL,
  `coupon_amount` varchar(255) NOT NULL,
  `coupon_dis_type` varchar(255) NOT NULL,
  `amount` float(8,2) NOT NULL,
  `cr_dr` enum('C','D') NOT NULL,
  `order_note` text NOT NULL,
  `created_on` datetime NOT NULL,
  `modified_on` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `cb_customer_credit_log` (`id`, `ord_id`, `coupon_assignee`, `coupon_assigned_to_id`, `coupon_id`, `coupon_code`, `coupon_amount`, `coupon_dis_type`, `amount`, `cr_dr`, `order_note`, `created_on`, `modified_on`) VALUES
(1, '3327', 'pranav.patel@cc.com', 'marmik.p@cc.com', '2953', 'demo_code1', '10', 'fixed_cart', 10.00, 'C', 'new order credit', '2019-05-15 11:48:18', '0000-00-00 00:00:00'),
(5, '3350', 'pranav.patel@cc.com', '-', '-', '-', '-', '-', 9.00, 'D', 'credit used by user', '2019-08-23 07:34:31', '0000-00-00 00:00:00'),
(9, '3359', 'pranav.patel@cc.com', 'marmik.p@cc.com', '2953', 'demo_code1', '10', 'fixed_cart', 0.50, 'C', 'new order credit', '2019-08-23 00:00:00', '0000-00-00 00:00:00'),
(11, '3373', 'pranav.patel@cc.com', '-', '-', '-', '-', '-', 1.00, 'D', 'credit used by user', '2019-08-23 12:47:35', '0000-00-00 00:00:00'),
(12, '3378', 'marmik.p@cc.com', 'jayesh.b@cc.com', '2953', 'demo_code1', '10', 'fixed_cart', 0.50, 'C', 'new order credit', '2019-08-23 13:00:20', '0000-00-00 00:00:00');





-----My Query -----
SELECT 
    SUM(COALESCE(CASE WHEN cr_dr = 'C' THEN amount END,0)) - 
    SUM(COALESCE(CASE WHEN cr_dr = 'D' THEN amount END,0)) balance 
    FROM 
        cb_customer_credit_log 
    WHERE 
        coupon_assignee = 'pranav.patel@cc.com'
     AND 
        created_on >= now() - interval 3 month 

对于过去三个月的数据，上述查询对我不起作用

Answer 1

您的回答是正确的。尝试以下查询:对于特定(有条件)用户:

select coupon_assignee, 
    SUM(COALESCE(CASE WHEN cr_dr = 'C' THEN amount END,0)) AS `Total Credit`,
    SUM(COALESCE(CASE WHEN cr_dr = 'D' THEN amount END,0)) AS `Total Debit`,
    ((SUM(COALESCE(CASE WHEN cr_dr = 'C' THEN amount END,0))) - (SUM(COALESCE(CASE 
    WHEN cr_dr = 'D' THEN amount END,0))))   AS `Total Balance` 
from cb_customer_credit_log
where coupon_assignee = 'pranav.patel@cc.com' 
AND created_on >= now() - interval 3 month;

对于所有用户:

select coupon_assignee, 
    SUM(COALESCE(CASE WHEN cr_dr = 'C' THEN amount END,0)) AS `Total Credit`,
    SUM(COALESCE(CASE WHEN cr_dr = 'D' THEN amount END,0)) AS `Total Debit`,
    ((SUM(COALESCE(CASE WHEN cr_dr = 'C' THEN amount END,0))) - (SUM(COALESCE(CASE 
    WHEN cr_dr = 'D' THEN amount END,0))))   AS `Total Balance` 
from cb_customer_credit_log
where created_on >= now() - interval 3 month
group by coupon_assignee;

对于组合用户:

select
    SUM(COALESCE(CASE WHEN cr_dr = 'C' THEN amount END,0)) AS `Total Credit`,
    SUM(COALESCE(CASE WHEN cr_dr = 'D' THEN amount END,0)) AS `Total Debit`,
    ((SUM(COALESCE(CASE WHEN cr_dr = 'C' THEN amount END,0))) - (SUM(COALESCE(CASE 
    WHEN cr_dr = 'D' THEN amount END,0))))   AS `Total Balance` 
from cb_customer_credit_log
where created_on >= now() - interval 3 month;