遇到错误: ORA-00979:不是按表达式00979分组的。00000 -“非一组表达”

Question

我想将3个表连接在一起，并将输出显示为below.But，当我尝试使用group和order在一起时遇到了错误。有什么解决办法可以解决这个错误吗？下面是我的Oracle查询。如果有人能帮助解决这个错误，我很感激。谢谢。

1. 数据库数据如下所示:这是我的第一个表(RMSTMP_PNG.ota_activity_lotinfo)：

ID KEY VALUE AD9FBCCC49F06446E050280A2 Quantity 100 AD9FBCCC49F06446E050280A2 PackageLeads BN1140 AD9FBCCC49F06446E050280A2 TestProgram DL\_FP5\_SLT AD9FBCCC49F06446E050280A2 TestRev 3.0.0 AD9FBCCC49F06446E050280A2 IDV 9651AA43

第二表如下(RMSTMP_PNG.ota_activity)：

EQP_ID  LOT_ID   ACTIVITY  MODIFIED BY  MODIFIED DATE              ID
LAS43   9KA2909  LOAD_LOT  jratnasa     18-AUG-20       AD9FBCCC49F06446E050280A2

第三表(RMSTMP_PNG.ota_activity_rescreen)

       ID                        RESCREEN TYPE
AD9FBCCC49F06446E050280A2           FRESH

1. 我想要的输出是将所有ID分组如下，并在网格视图中显示，但我不确定是否可以这样做？

最后的输出应该如下所示：

EQP_ID   MODIFIED DATE         ID                            name
LAS43    18-AUG-20       AD9FBCCC49F06446E050280A2        Quantity:100,
                                                          PackageLeads:BN1140,
                                                          TestProgram:DL_FP5_SLT,  
                                                          TestRev:3.0.0,                                
                                                          IDV:9651AA43

1. 我想出了这个查询，但似乎不起作用。

SELECT RMSTMP_PNG.ota_activity.EQP_ID,
       RMSTMP_PNG.ota_activity.MODIFIED_DATE,
       RMSTMP_PNG.ota_activity_lotinfo.ID,
       LISTAGG(RMSTMP_PNG.ota_activity_lotinfo.KEY) 
              WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY) as "names"
from RMSTMP_PNG.ota_activity 
INNER JOIN RMSTMP_PNG.ota_activity_rescreen 
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID 
INNER JOIN RMSTMP_PNG.ota_activity_lotinfo 
    ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID 
WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN'01-Jan-2020'AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity_lotinfo.KEY,
         RMSTMP_PNG.ota_activity.EQP_ID
order by RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

Answer 1

试试下面。始终使用SELECT和GROUP BY语句检查ORA-00979: not a GROUP BY语句

这里 LISTAGG的其他用法，以防您需要它们。

  SELECT RMSTMP_PNG.ota_activity.EQP_ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity_lotinfo.ID,
         LISTAGG (RMSTMP_PNG.ota_activity_lotinfo.KEY)
            WITHIN GROUP (ORDER BY RMSTMP_PNG.ota_activity_lotinfo.KEY)
            AS "names"
    FROM RMSTMP_PNG.ota_activity
         INNER JOIN RMSTMP_PNG.ota_activity_rescreen
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_rescreen.ID
         INNER JOIN RMSTMP_PNG.ota_activity_lotinfo
            ON RMSTMP_PNG.ota_activity.ID = RMSTMP_PNG.ota_activity_lotinfo.ID
   WHERE RMSTMP_PNG.ota_activity.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.ota_activity_lotinfo.ID,
         RMSTMP_PNG.ota_activity.MODIFIED_DATE,
         RMSTMP_PNG.ota_activity.EQP_ID
ORDER BY RMSTMP_PNG.ota_activity.MODIFIED_DATE ASC

您的预期输出将在下面进行尝试。我不知道您需要什么，值列来自哪里。我知道你能应付得来。

 SELECT RMSTMP_PNG.OTA_ACTIVITY.EQP_ID AS ID,
         LISTAGG (RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.KEY || ':' || VALUE, ',')
            WITHIN GROUP (ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE)
            AS "names"
    FROM RMSTMP_PNG.OTA_ACTIVITY
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_RESCREEN
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_RESCREEN.ID
         INNER JOIN RMSTMP_PNG.OTA_ACTIVITY_LOTINFO
            ON RMSTMP_PNG.OTA_ACTIVITY.ID = RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
   WHERE RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE BETWEEN '01-Jan-2020'
                                                   AND '30-Sep-2020'
GROUP BY RMSTMP_PNG.OTA_ACTIVITY_LOTINFO.ID
ORDER BY RMSTMP_PNG.OTA_ACTIVITY.MODIFIED_DATE