我有这张桌子:
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,所以不能使用window函数或“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'之间的交易日期,这将是所需的输出:
| 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 |