當 select 陳述句中存在溢價時，“溢價”列出現錯誤。我不確定我做錯了什么。
SELECT QUIKPLAN.FORMS, COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING ((QUIKPLAN.FORMS)='L-4054' and ((PREMIUM)>1))
此代碼在 Snowflake 中有效，但在 SQL Server 中無效。謝謝你的幫助。
uj5u.com熱心網友回復：
正如上面評論中提到的，您不能訪問同一查詢范圍內的計算欄位。但是，您可以使用相同的計算兩次而不會受到懲罰。
SELECT
QUIKPLAN.FORMS,
COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate,
ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM
QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING ((QUIKPLAN.FORMS)='L-4054' and ((ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0))>1))
uj5u.com熱心網友回復：
你需要這樣做：
SELECT
QUIKPLAN.FORMS
, COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate
, ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1
ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
WHERE (QUIKPLAN.FORMS)='L-4054'
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) >1
uj5u.com熱心網友回復：
您可以為此使用 CTE
WITH CTE AS (SELECT QUIKPLAN.FORMS, COALESCE(NEWQUIKVALF1.MVALDATE, '2022-09-30') AS ReportDate, ISNULL((SUM(NEWQUIKVALF1.MANNLZD)/12),0) AS PREMIUM
FROM QUIKPLAN
LEFT JOIN dbo.NEWQUIKVALF1 ON QUIKPLAN.NEWPLAN=NEWQUIKVALF1.NPLAN
GROUP BY QUIKPLAN.FORMS, NEWQUIKVALF1.MVALDATE
HAVING QUIKPLAN.FORMS ='L-4054' )
SELECT FORMS, ReportDate,PREMIUM
FROM CTE
WHERE PREMIUM>1
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/533972.html
標籤：数据库sql服务器