SQL 查询给出“不是 GROUP BY 表达式”错误

Question

SELECT SUM(a.Clicks) AS Clicks, SUM(b.NoOfUsers) Users,  c.WEEK_NUM, b2.ALL_TASKS  FROM     
     (SELECT SUM(CLICK_CNT) AS Clicks, TO_CHAR(RQST_DT,'YYYY-MM-DD') AS DATEE FROM PER_DAY_USAGE    GROUP BY RQST_DT) a,    
     (SELECT TO_CHAR(RQST_DT,'YYYY-MM-DD') AS DATEEE, WEEK_NUM FROM TIMEDIM) c,     
     (SELECT NoOfUsers, accDate FROM (( SELECT  COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers,  TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') AS accDate  FROM    ALRT_HSTRY WHERE ACTN_TAKE_CD is not null   GROUP BY TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')) UNION ALL (   SELECT  COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers,  TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD') AS accDate  FROM    PLATFORM_ALRT WHERE ACTN_TAKE_CD is not null    GROUP BY TO_CHAR(ACTN_TAKE_DATA_TM,'YYYY-MM-DD')))          ) b, 
     ( select sum(TOTL_ALRT_CNT) ALL_TASKS,  TO_CHAR(CRTE_ON_DT,'YYYY-MM-DD') AS DATEE  from FEED_HSTRY where APPL_CD like '%' group by CRTE_ON_DT)b2 
     WHERE   a.datee = b.accDate AND  a.datee=c.dateee AND b2.DATEE = c.dateee GROUP BY c.WEEK_NUM

这是 b2.ALL_TASKS ALL_TASKS 的问题。如果我从 SELECT 中删除它，它就会起作用。

Answer 1

您将 b2.ALL_TASKS 添加到最终的group by 中，但您应该使用现代 JOIN 语法并尝试更好地格式化它：

SELECT 
    SUM(a.Clicks) AS Clicks, SUM(b.NoOfUsers) Users,  c.WEEK_NUM, b2.ALL_TASKS  
    FROM (SELECT
              SUM(CLICK_CNT) AS Clicks, RQST_DT AS DATEE 
              FROM PER_DAY_USAGE    
              GROUP BY RQST_DT
         ) a
        INNER JOIN  (SELECT
                         RQST_DT AS DATEEE, WEEK_NUM 
                         FROM TIMEDIM
                    ) c ON a.datee=c.dateee
        INNER JOIN  (SELECT
                         NoOfUsers, accDate 
                         FROM (
                                  (SELECT
                                       COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers,  ACTN_TAKE_DATA_TM AS accDate  
                                       FROM ALRT_HSTRY 
                                       WHERE ACTN_TAKE_CD is not null
                                       GROUP BY ACTN_TAKE_DATA_TM
                                  )
                                  UNION ALL
                                  (SELECT
                                       COUNT(DISTINCT RECEPIENT_ID) AS NoOfUsers,  ACTN_TAKE_DATA_TM AS accDate
                                       FROM PLATFORM_ALRT 
                                       WHERE ACTN_TAKE_CD is not null
                                       GROUP BY ACTN_TAKE_DATA_TM
                                  )
                              )
                    ) b ON a.datee = b.accDate
        INNER JOIN  (SELECT
                         sum(TOTL_ALRT_CNT) ALL_TASKS,  CRTE_ON_DT AS DATEE  
                         from FEED_HSTRY 
                         where APPL_CD like '%' 
                         group by CRTE_ON_DT
                    ) b2 ON c.dateee=b2.DATEE
    GROUP BY c.WEEK_NUM, b2.ALL_TASKS