在圖書館資料庫中，我應該找到逾期貸款的百分比。
(
LoanID int not null,
Cop_Av int not null, -- value 1-10
LoanDt datetime not null,
Cop_OnLoan int not null, -- VALUE 1 or 2
MemID int not null, -- VALUE BTWN 1-1200
DueDt datetime not null,
ReturnDt datetime not null
)
;
SELECT concat((OverDue/Total)*100) AS Percentage_OverDue
FROM (SELECT COUNT(*) AS Total
FROM loanlist
INNER JOIN (
SELECT COUNT(*) AS OverDue
FROM loanlist
WHERE ReturnDt > DueDt) )
;```
I have the aliases, but why is it still showing (Error Code: 1248. Every derived table must have its own alias )?
I also tried
SELECT concat((OverDue/Total) 100) AS Percentage_OverDue FROM (SELECT COUNT( ) AS Total FROM loanlist INNER JOIN (SELECT COUNT(*) AS OverDue FROM loanlist WHERE ReturnDt > DueDt) as a) as b;
But it gave another error; Error Code: 1054. Unknown column 'OverDue' in 'field list'
What am I missing?
uj5u.com熱心網友回復：
就像錯誤訊息所暗示的那樣，您需要為派生表提供別名。在您的查詢中，有兩個派生表缺少它們的別名。此外，在 INNER JOIN 之上使用聚合函式并不是這里最謹慎的選擇。嘗試使用子查詢在 SELECT 子句中回傳一個值。
SELECT concat((OverDue/Total)*100) AS Percentage_OverDue
FROM (SELECT COUNT(*) AS Total,
(SELECT COUNT(*) -- This subquery
FROM loanlist -- returns a value
WHERE ReturnDt > DueDt) AS OverDue -- in the SELECT list
FROM loanlist ) t -- The derived table t will be used as the source table for the main query.
;
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/496281.html
標籤：mysql