我需要从最早的日期获取开始余额,并从月末开始获取结束余额,并按stock_id分组。
我的桌子:
id stock_id balance transact_at
1 1 100 2018-06-15
2 1 70 2018-06-16
3 1 30 2018-06-31
4 2 50 2018-06-01
5 2 10 2018-03-31
我想要输出:
stock_id start_balance ending_balance
1 100 30
2 50 10
最佳答案
试试这个。在这两个内部查询中,通过获取对应于stock_id的最小和最大transact_at来获取期初余额和期末余额,然后父查询将这两个查询合并以在一行中获取期初和期末余额。我也分享了下面的小提琴链接来尝试。
select
tabledata1.stock_id,
startBalance,
closingBalance
from (
select
table1.stock_id,
balance as startBalance
from table1 join
(
select stock_id,
min(transact_at) as transact_at
from Table1 group by stock_id
) startTransaction
on Table1.stock_id = startTransaction.stock_id and
Table1.transact_at = startTransaction.transact_at
) tabledata1
join (
select
table1.stock_id,
balance as closingBalance
from table1 join
(
select stock_id,
max(transact_at) as transact_at
from Table1 group by stock_id
) endTransaction
on Table1.stock_id = endTransaction.stock_id
and Table1.transact_at = endTransaction.transact_at
) tabledata2
on tabledata1.stock_id = tabledata2.stock_id;
Demo
关于mysql - 需要有关MySQL查询的帮助,我需要按stock_id按日期分组获取期初余额和期末余额,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/50902741/