我需要使用 mysql 查詢進行計算。
這是我的查詢。
SELECT book_name,
CASE WHEN CURDATE()<book_return THEN 0 ELSE DATEDIFF(CURDATE(),book_return) END AS DateDifference,
CASE WHEN DateDifference>0 THEN DateDifference*10 ELSE NULL) END AS TotalFines FROM tblIssuedBooks order by lastupdated DESC
如果 DateDIfference 值大于零，我需要將 DateDifference 列乘以 10。但是當我執行此操作時，我得到 Unknown column 'DateDifference' in 'field list'一個錯誤。
有人可以告訴我如何改進嗎？
uj5u.com熱心網友回復：
您不能在同一選擇中定義的選擇中重用別名。這里的一種解決方法是使用子查詢：
SELECT book_name, DateDifference,
CASE WHEN DateDifference > 0 THEN DateDifference*10 END AS TotalFines
FROM
(
SELECT *, CASE WHEN CURDATE() < book_return
THEN 0
ELSE DATEDIFF(CURDATE(), book_return) END AS DateDifference
FROM tblIssuedBooks
) t
ORDER BY lastupdated DESC;
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/483435.html