mysql中的一种方法是通过 stock_id 一次，找出开盘和收盘日期。然后，自联接两次，以提取在这些开始日期和结束日期发生的实际余额。

SELECT
    t1.stock_id,
    t2.balance AS start_balance,
    t3.balance AS ending_balance
FROM
(
    SELECT
        stock_id,
        MIN(transact_at) AS min_transact_at,
        MAX(transact_at) AS max_transact_at
    FROM my_table
    GROUP BY stock_id
) t1
INNER JOIN my_table t2
    ON t1.stock_id = t2.stock_id AND t2.transact_at = t1.min_transact_at
INNER JOIN my_table t3
    ON t1.stock_id = t3.stock_id AND t3.transact_at = t1.max_transact_at;

演示

注意：为了子孙后代，当mysql 8+成为标准时，我们可以使用 ROW_NUMBER 在这里，可能更容易得到我们想要的结果。

试试这个。其中两个内部查询通过获取股票id对应的最小和最大交易量来获取期初余额和期末余额，然后父查询将这两个查询组合在一起以获得单行中的期初余额和期末余额。我也分享了小提琴下面的链接来尝试。

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;

演示

试试这个。

SELECT stock_id,MAX(balance) as start_balance, MIN(balance) as ending_balance  FROM tbl_balance GROUP BY stock_id