我正在嘗試從以下代碼創建一個程式：（在內部使用另一個程式）
SET @p0='87';
CALL `wm_ewallet_transactions_balance`(@p0);
with recursive rcte(user_id, balance, date) as (
(
select user_id, balance, date
from wm_ewallet_user_balance
order by date
limit 1
)
union all
select coalesce(t.user_id, r.user_id),
coalesce(t.balance, r.balance),
r.date interval 1 day
from rcte r
left join wm_ewallet_user_balance t on t.date = r.date interval 1 day
where r.date < (select max(date) from wm_ewallet_user_balance)
)
select r.user_id, min(r.balance) as balance, r.date
from rcte r
group by r.user_id, r.date
order by r.date;
如您所見，在第二行中，我呼叫了另一個名為wm_ewallet_transactions_balance. 它創建了一個名為的臨時表wm_ewallet_user_balance（我已經使用了該表三次）
問題是它拋出：
#1137 - 無法重新打開表：'wm_ewallet_user_balance'
我該如何解決？
這是名為的第一個程序wm_ewallet_transactions_balance：
CREATE TEMPORARY TABLE wm_ewallet_user_balance
(
INDEX user_id_index (user_id),
INDEX created_at_index (date)
)
SELECT
id,
user_id,
type,
amount,
SUM(amount) OVER(PARTITION BY user_id ORDER BY id) AS `balance`,
created_at,
date(created_at) AS date
FROM
wm_ewallet_transactions
WHERE user_id = user_id_param
uj5u.com熱心網友回復：
它不漂亮并且不使用索引，因此您必須檢查生成三倍的臨時表是否使其更快
WITH wm_ewallet_user_balance AS
(
SELECT
id,
user_id,
type,
amount,
SUM(amount) OVER(PARTITION BY user_id ORDER BY id) AS `balance`,
created_at,
date(created_at) AS date
FROM
wm_ewallet_transactions
WHERE user_id = @p0)
SELECT * FROM (
WITH recursive rcte(user_id, balance, date) as (
(
select user_id, balance, date
from wm_ewallet_user_balance
order by date
limit 1
)
union all
select coalesce(t.user_id, r.user_id),
coalesce(t.balance, r.balance),
r.date interval 1 day
from rcte r
left join wm_ewallet_user_balance t on t.date = r.date interval 1 day
where r.date < (select max(date) from wm_ewallet_user_balance)
)
select r.user_id, min(r.balance) as balance, r.date
from rcte r
group by r.user_id, r.date
order by r.date) t1;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/526887.html
標籤：mysqlsql存储过程