mysql - 使用 group by 子句运行多个类别的总和

Question

我有这张表:

CREATE TABLE tbl_sample
(
ID SERIAL PRIMARY KEY 
,sale decimal(10,2)
,pc varchar(45)
,trans_date date
);

 INSERT INTO tbl_sample
 VALUES 
 (1,100, 'p1','2019-08-27' ),(2,150, 'p2', '2019-08-27'),(3,175,'p1', '2019-08-27')
,(4,250, 'p2', '2019-08-28'),(5,100, 'p2', '2019-08-28'),(6,88,'p1', '2019-08-28')
,(7,120, 'p1', '2019-08-29'),(8,130,'p1', '2019-08-29'),(9,275,'p2', '2019-08-29');

这个查询:

 select pc, trans_date, (select sum(x.sale) from tbl_sample x where x.id <= t.id and  t.pc = 
 x.pc) - sale as accum_sales_beginning, sale, (select sum(x.sale) from tbl_sample x where 
 t.pc = x.pc and x.id <= t.id) as accum_sales_ending from tbl_sample t  where t.trans_date 
 between '2019-08-27' and '2019-08-29' and t.pc = 'p1';

有一个结果:

  | pc | trans_date | accum_sales_beginning |  sale   | accum_sales_ending |
  --------------------------------------------------------------------------
  | p1 | 2019-08-27 |         0.00          |  100.00 |      100.00        |
  | p1 | 2019-08-27 |       100.00          |  175.00 |      275.00        |
  | p1 | 2019-08-28 |       275.00          |   88.00 |      363.00        |
  | p1 | 2019-08-29 |       363.00          |  120.00 |      483.00        |
  | p1 | 2019-08-29 |       483.00          |  130.00 |      613.00        |

现在，当我使用“group by”子句时:

  select t.pc, t.trans_date, (select sum(x.sale) from tbl_sample x where x.id <= t.id and  
  t.pc = x.pc) - sum(sale) as accum_sales_beginning, sum(sale), (select sum(x.sale) from 
  tbl_sample x where t.pc = x.pc and x.id <= t.id) as accum_sales_ending from tbl_sample t  
  where t.trans_date between '2019-08-27' and '2019-08-29' and t.pc = 'p1' group by 
  t.trans_date;

它给我一个错误。我正在使用 mysql 5.7，因此不能使用窗口函数或“sum() over”。下面是我想要的输出。这可能吗？

  | pc | trans_date | accum_sales_beginning |  sale   | accum_sales_ending |
  --------------------------------------------------------------------------
  | p1 | 2019-08-27 |         0.00          |  275.00 |      275.00        |
  | p1 | 2019-08-28 |       275.00          |   88.00 |      363.00        |
  | p1 | 2019-08-29 |       363.00          |  250.00 |      613.00        |

此外，如果我将使用“2019-08-28”和“2019-08-29”之间的 trans_date 进行查询，这将是所需的输出:

  | pc | trans_date | accum_sales_beginning |  sale   | accum_sales_ending |
  --------------------------------------------------------------------------
  | p1 | 2019-08-28 |       275.00          |   88.00 |      363.00        |
  | p1 | 2019-08-29 |       363.00          |  250.00 |      613.00        |

Answer 1

试试下面的代码

SELECT 
t.pc, 
t.trans_date, 
(
    SELECT SUM(x.sale) FROM tbl_sample x
    WHERE x.id <= MAX(t.id) AND t.pc = x.pc
    -- Just used MAX(t.id) in place of t.id
    -- As group by is available in the query
) - 
SUM(sale) AS accum_sales_beginning, 
SUM(sale),
(
    SELECT SUM(x.sale) FROM tbl_sample x
    WHERE t.pc = x.pc AND x.id <= MAX(t.id)
    -- Just used MAX(t.id) in place of t.id
    -- As group by is available in the query
) AS accum_sales_ending

FROM tbl_sample t
WHERE t.trans_date BETWEEN '2019-08-27' AND '2019-08-29'
      AND t.pc = 'p1'
GROUP BY t.pc,t.trans_date;