View 的FROM 子句中的子查詢故障排除
MySQL 視圖的SELECT 語句出現「View's SELECT contains a subquery in the FROM Clause」錯誤FROM 子句中包含子查詢。根據 MySQL 文檔,這是不允許的。
要在建立名為view_credit_status 的視圖的查詢中解決此問題,請考慮以下解決方案:
有問題的子查詢是:
(select credit_usage.client_id, sum(credits_used) as credits_used from credit_usage group by credit_usage.client_id) as t0
為此子查詢建立一個單獨的視圖:
create view view_credit_usage_summary as select credit_usage.client_id, sum(credits_used) as credits_used from credit_usage group by credit_usage.client_id
在view_credit_status中視圖,引用新的 view_credit_usage_summary 視圖而不是子查詢:
create view view_credit_status as (select credit_orders.client_id, sum(credit_orders.number_of_credits) as purchased, ifnull(view_credit_usage_summary.credits_used,0) as used from credit_orders left outer join view_credit_usage_summary on view_credit_usage_summary.client_id = credit_orders.client_id where credit_orders.payment_status='Paid' group by credit_orders.client_id)
這種方法將子查詢分離到專用視圖中,允許您在不違反 MySQL 限制的情況下存取其結果。
以上是如何解決MySQL中「視圖的SELECT在FROM子句中包含子查詢」?的詳細內容。更多資訊請關注PHP中文網其他相關文章!