带SUM和子查询的MySQL累计总数

ratherBeKiting • 6 年前 • 1406 次点击

我有下表:

CREATE TABLE `_loans` (
  `loan_id` int(11) NOT NULL,
  `price` float(7,2) NOT NULL,
  `term` int(11) NOT NULL,
  `app_date` date NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `_loans` (`loan_id`, `price`, `term`, `app_date`) VALUES
(1, 299.00, 24, '2019-01-23'),
(2, 774.00, 24, '2019-01-24'),
(3, 817.80, 24, '2019-01-24'),
(4, 279.99, 24, '2019-01-28'),
(5, 463.99, 24, '2019-01-28'),
(6, 0.00, 24, '2019-01-28'),
(7, 357.00, 24, '2019-02-02'),
(8, 386.98, 24, '2019-02-04'),
(9, 846.00, 24, '2019-02-04'),
(10, 713.99, 24, '2019-02-06'),
(11, 0.00, 24, '2019-02-07'),
(12, 579.00, 24, '2019-02-11'),
(13, 179.00, 24, '2019-02-13'),
(14, 0.00, 24, '2019-02-19'),
(15, 259.00, 24, '2019-02-21'),
(16, 249.99, 24, '2019-02-26'),
(17, 319.00, 24, '2019-03-02'),
(18, 1108.99, 24, '2019-03-05'),
(19, 319.00, 24, '2019-03-05'),
(20, 199.97, 24, '2019-03-06');

ALTER TABLE `_loans`
  ADD PRIMARY KEY (`loan_id`),
  ADD KEY `app_date` (`app_date`);

以及下面的查询,它给出了表数据的每月摘要:

SELECT 
MONTHNAME(w.app_date) month,
YEAR(w.app_date) year,
COUNT(*) contracts,
SUM(w.total_price) totals,
@running_total := @running_total + SUM(w.total_price) running_totals
FROM (
    SELECT 
        app_date,
        SUM(price * term) total_price
    FROM _loans l
    GROUP BY l.loan_id
) w
JOIN (SELECT @running_total := 0) r
GROUP BY YEAR(w.app_date), MONTH(w.app_date) 
ORDER BY YEAR(w.app_date), MONTH(w.app_date) ASC

我需要“总计”列中的值的连续合计。除了累积的总数之外,一切都很好地工作,而累积的总数不是累积的。

month     | year   |  contracts |    totals    |    running_totals  
------------------------------------------------------------------
January   | 2019   |      6     |   63234.72   |    63234.71923828125
February  | 2019   |     10     |   85703.04   |    85703.04016113281
March     | 2019   |      4     |   46727.04   |    46727.039794921875

Python社区是高质量的Python/Django开发社区
本文地址：http://www.python88.com/topic/30353

1406 次点击

文章 [ 2 ] | 最新文章 6 年前

• 1 楼

Gordon Linoff 6 年前

您的查询过于复杂。聚合不需要子查询。但是,有必要使用变量:

SELECT month, year, total_price,
       (@running_total := @running_total + total_price) as running_totals
FROM (SELECT MONTHNAME(l.app_date) as month,
             YEAR(l.app_date) as year,
             MONTH(l.app_date) as mon,
             COUNT(DISTINCT l.loan_id) as contracts,
             SUM(l.price * l.term) total_price
      FROM _loans l
      GROUP BY l.loan_id, YEAR(w.app_date), MONTH(w.app_date)
      ORDER BY l.loan_id, YEAR(w.app_date), MONTH(w.app_date)
     ) l CROSS JOIN
     (SELECT @running_total := 0) params
ORDER BY year, mon ASC

• 2 楼

Nick Loay Oraby 6 年前

这不起作用,因为 GROUP 惯性导航与制导。你需要把 组 作为子查询,并根据该查询计算运行总数:

SELECT monthname as month, year, contracts, totals,
       @running_total := @running_total + totals AS running_totals
FROM (SELECT 
          MONTHNAME(w.app_date) monthname,
          MONTH(w.app_date) monthnum,
          YEAR(w.app_date) year,
          COUNT(*) contracts,
          SUM(w.total_price) totals
      FROM (SELECT 
                app_date,
                SUM(price * term) total_price
                FROM _loans l
                GROUP BY l.loan_id
      ) w
      GROUP BY year, monthnum, monthname
) t
JOIN (SELECT @running_total := 0) r
ORDER BY year, monthnum ASC

输出:

month       year    contracts   totals      running_totals
January     2019    6           63234.72    63234.71923828125
February    2019    10          85703.04    148937.75939941406
March       2019    4           46727.04    195664.79919433594

Demo on dbfiddle

登录后回复