select - DB2:通过干扰 Select 子句中的子查询进行分组

Question

我被 SQL 查询困住了。我在 select 子句中进行子查询，db2 提示 -119 错误提示:

"An expression starting with "FACILITY_ID" specified in a SELECT clause, HAVING clause, or ORDER BY clause is not specified in the GROUP BY clause or it is in a SELECT clause, HAVING clause, or ORDER BY clause with a column function and no GROUP BY clause is specified. SQL Code: -119, SQL State: 42803"

我不知道如何在 GROUP BY 中包含 NUMTAGS。我尝试在 GROUP BY 子句中包含 BC.ITEM 但没有结果。我不知道如何在网络上找到这个，我看的论坛也没有用。

如果有人可以向我解释如何修复此问题或粘贴链接以供我寻找解决方案，我将不胜感激。

SQL 查询与 DB2 相关，位于此处:

SELECT SS.NAME, B.DESTINATION_ID, P.DESCRIPTION, B.LAST_READ_DATE, B.LABEL, 
        (SELECT COUNT(BC.ITEM) 
        FROM PROJECT_DB.BOX_CONTENT BC
        WHERE BC.FACILITY_ID = B.FACILITY_ID
        AND BC.BOX_ID = B.BOX_ID
        GROUP BY BC.ITEM) AS NUMTAGS,
        B.BOX_ID
FROM PROJECT_DB.BOX B
INNER JOIN MAINDB.DESTINATION P ON B.DESTINATION_ID = P.DESTINATION_ID
INNER JOIN MAINDB.WAREHOUSE_DESTINATION_SECTION WH 
                                ON B.DESTINATION_ID = WH.DESTINATION_ID
                                AND B.WAREHOUSE_ID = WH.WAREHOUSE_ID
INNER JOIN MAINDB.SECTION S ON S.SECTION = WH.SECTION   
                            AND S.SECTION_TYPE = B.SECTION_TYPE
INNER JOIN MAINDB.DESTINATION_SET SS 
                            ON SS.DESTINATION_SET_ID = S.DESTINATION_SET_ID
WHERE B.WAREHOUSE_ID = 100
GROUP BY B.BOX_ID, B.FACILITY_ID, B.DESTINATION_ID, B.LAST_READ_DATE, B.LABEL, P.DESCRIPTION, SS.NAME

Answer 1

You can correct the statement by: v including the columns in the GROUP BY clause that are in the SELECT clause, or v removing the columns from the SELECT clause.

来自:DB2 文档。

您需要将所有 选定的列放入您的 GROUP BY...