SQL语句中，WHERE子句不能有聚合函数有关问题_SQL

SQL语句中，WHERE子句不能有聚合函数问题

一般SQL语句中，WHERE子句是不能含有聚合函数的，否则报错：group function is not allowed here，那么我们如何去处理呢？

如下SQL：

SELECT
   T1.Loan_Contract_Id              AS Contract_Id
   ,CAST('20111231' AS DATE FORMAT 'YYYYMMDD' ) AS Statis_Dt
   ,T1.Agmt_Holder                               AS Cust_Id
   ,T1.Loan_Amt                                  AS Contract_Amt
   ,null                                         AS ApplicantApp_Loan_Amt
   ,T1.Adv_Dt                                    AS Contr_Eff_Time
   ,T1.Mature_Dt                                 AS Contr_End_Time
   ,case when cast(substr(T1.Loan_Term,5,2) as integer) >0 then cast(substr(T1.Loan_Term,1,2) as integer)*12+cast(substr(T1.Loan_Term,3,2) as integer)+1
   else cast(substr(T1.Loan_Term,1,2) as integer)*12+cast(substr(T1.Loan_Term,3,2) as integer) end AS Contr_Term_Month
   ,null                                         AS Margin_Amt
FROM DEV_DW_RDL.R04_B_IL_Contract T1
WHERE
   T1.Data_Dt=cast('20111231' as date format 'YYYYMMDD')
AND (T1.Loan_Appl_Approve_Stat_Cd='5' OR (T1.Loan_Appl_Approve_Stat_Cd='4' AND T1.Loan_Contract_Id IN (SELECT Approve_Content_Id FROM DW_PVIEW.T05_IL_Approve_Evt WHERE MAX(Txn_Dt)=CAST('20111231' as date format 'YYYYMMDD'))))
;

因为WHERE子句中含有MAX聚合函数，会报SELECT Failed. 3569: Improper use of an aggregate function in a WHERE Clause.错误。

作如下修改：

SELECT
   T1.Loan_Contract_Id              AS Contract_Id
   ,CAST('20111231' AS DATE FORMAT 'YYYYMMDD' ) AS Statis_Dt
   ,T1.Agmt_Holder                               AS Cust_Id
   ,T1.Loan_Amt                                  AS Contract_Amt
   ,null                                         AS ApplicantApp_Loan_Amt
   ,T1.Adv_Dt                                    AS Contr_Eff_Time
   ,T1.Mature_Dt                                 AS Contr_End_Time
   ,case when cast(substr(T1.Loan_Term,5,2) as integer) >0 then cast(substr(T1.Loan_Term,1,2) as integer)*12+cast(substr(T1.Loan_Term,3,2) as integer)+1
   else cast(substr(T1.Loan_Term,1,2) as integer)*12+cast(substr(T1.Loan_Term,3,2) as integer) end AS Contr_Term_Month
   ,null                                         AS Margin_Amt
FROM DEV_DW_RDL.R04_B_IL_Contract T1
WHERE
   T1.Data_Dt=cast('20111231' as date format 'YYYYMMDD')
AND (T1.Loan_Appl_Approve_Stat_Cd='5'
           OR (T1.Loan_Appl_Approve_Stat_Cd='4'
                    AND T1.Loan_Contract_Id IN ( SELECT tmp.Approve_Content_Id FROM
                    (SELECT Approve_Content_Id,MAX(Txn_Dt) AS Max_Txn_Dt FROM DW_PVIEW.T05_IL_Approve_Evt GROUP BY Approve_Content_Id) tmp
                    WHERE tmp.Max_Txn_Dt=CAST('20111231' as date format 'YYYYMMDD'))))
;

将聚合函数置于子集中，那么问题就迎刃而解了。