SELECT item, `date`, 
       MAX(CASE WHEN first_last.opening = test.trasactionid THEN test.trasactionid END) OpeningId,
       MAX(CASE WHEN first_last.opening = test.trasactionid THEN quantity END) OpeningBal, 
       MAX(CASE WHEN first_last.closing = test.trasactionid THEN test.trasactionid END) ColsingId,
       MAX(CASE WHEN first_last.closing = test.trasactionid THEN quantity END) ClosingBal
FROM test
JOIN (
  SELECT item, `date`, MIN(trasactionid) opening, MAX(trasactionid) closing
  FROM test
  GROUP BY 1,2
) first_last USING (item, `date`)
GROUP BY 1,2

https://dbfiddle.uk/QQlpAnJt

从版本5.5开始，这是一个适用于任何mysql版本的工作解决方案：

select t.Item, t.Date, t.opening_transaction, op.Quantity as OpeningBal, t.closing_transaction, oc.Quantity as ClosingBal 
from (
  select Item, Date, min(transactionid) as opening_transaction,
                   max(transactionid) as closing_transaction
  from mytable
  group by item, Date
) t
inner join mytable op on op.transactionid = t.opening_transaction
inner join mytable oc on oc.transactionid = t.closing_transaction

Demo here

使用first_value和last value窗口函数，尝试以下操作：

select Item, Date_, transactionid_1, OpeningBal, transactionid_2, ClosingBal
from
(
  select Item, Date_, 
   min(transactionid) over (partition by Item, Date_) transactionid_1,
   first_value(Quantity) over (partition by Item, Date_ order by transactionid) OpeningBal,
   max(transactionid) over (partition by Item, Date_) transactionid_2,
   last_value(Quantity) over (partition by Item, Date_ order by transactionid range between unbounded preceding and unbounded following) ClosingBal,
  transactionid
 from table_name
) t
where transactionid = transactionid_1
order by Item, Date_

demo

你可以像这样使用窗口函数：

select item, date, 
    transation_id opening_transaction_id, balance opening_quantity,
    closing_transaction_id, closing_quantity
from (
    select t.*, 
        max(transation_id)    over(partition by item, date) closing_transaction_id,
        first_value(quantity) over(partition by item, date order by transaction_id desc) closing_quantity,
        row_number()          over(partition by item, date order by transaction_id) rn
    from mytable t
) t
where rn = 1

该子查询分别使用max()和first_value()计算最后一次每日交易的日期和数量。它还使用row_number()标识与第一个事务对应的行。然后，外部查询只过滤每个组的顶部记录，它已经包含了我们需要的信息。
这种方法的优点是它避免了聚合和自连接（在大多数情况下效率较低），并且它具有尽可能少的窗口函数调用。

我不确定mySql语法，但许多SQL引擎支持WITH语法，我认为这将是非常容易为您翻译。这是解决您的问题的一种方法：

WITH min_max AS (
  SELECT item, date, min(tran_id) AS min_id, max(tran_id) AS max_id
    FROM my_table
   GROUP BY item, date
)
SELECT item, date, min_id, my_table1.quantity, max_id, my_table2.quantity
  FROM min_max
  JOIN my_table AS my_table1
    ON min_max.min_id = my_table1.tran_id
  JOIN my_table AS my_table2
    ON min_max.max_id = my_table2.tran_id

如果不支持WITH语法，可以使用FROM (SELECT ...)语法
希望有帮助