文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >HSQL视图错误:不按列聚合或分组的表达式

问HSQL视图错误:不按列聚合或分组的表达式
EN

Stack Overflow用户
提问于 2012-07-24 09:22:13
回答 1查看 9K关注 0票数 0

我在hsql中有一个CREATE查询，但是每当我运行它时，它都会抛出这个错误：

不按列聚合或分组的表达式: AGV.ID

我理解组BY在没有任何聚合表达式(AVG、SUM、MIN、MAX)的情况下是不会工作的，但我不知道如何修复我的查询。因为每个记录都需要按manifestID值分组。

基本上，我试图通过合并3组select查询来创建一个视图。

我尝试使用不同的，但没有运气，因为如果我有多个选定的列，它将无法工作。此查询在MYSQL中运行良好。

我的问题是：

代码语言：javascript
复制
CREATE VIEW local_view_event_manifest(
        manifest_id,
        eventId, 
        eventType, 
        eventDate,
        manifestID, 
        businessStepStr, 
        manifestVersion,
        externalLocation, 
        remark, 
        epcCode, 
        locationCode
        )
        AS
        SELECT 
        agm.manifest_id AS manifest_id,
        agv.id AS eventId,
        'AGGREGATION_EVENT' AS eventType,
        agv.event_time AS eventDate,
        md.manifest_id AS manifestID,
        agv.business_step_code AS businessStepStr,
        md.manifest_version AS manifestVersion,
        md.external_location AS externalLocation,
        md.remark AS remark,
        epc.code as epcCode,
        bloc.location_code as locationCode
        FROM 
        "local".local_MANIFEST_DATA AS md,
        "local".local_AGGREGATION_EVENT AS agv,
        "local".local_AGGREGATION_EVENT_EPCS AS agv_epc,
        "local".local_EPC AS epc,
        "local".local_BUSINESS_LOCATION AS bloc,
        "local".local_AGGREGATION_EVENT_MANIFEST_DATA AS agm
        WHERE
        md.id=agm.manifest_id
        AND agv.deleted=0
        AND md.deleted=0
        AND agv.id=agm.aggregation_event_id
        AND agv.id=agv_epc.aggregation_event_id
        AND agv.business_location_id=bloc.id
        AND bloc.id=agv.business_location_id
        AND agv_epc.epc_id=epc.id
        GROUP BY agm.manifest_id

        UNION
        SELECT 
        om.manifest_id AS manifest_id,
        ov.id AS eventId,
        'OBJECT_EVENT' AS eventType,
        ov.event_time AS eventDate,
        md.manifest_id AS manifestID,
        ov.business_step_code AS businessStepStr,
        md.manifest_version AS manifestVersion,
        md.external_location AS externalLocation,
        md.remark AS remark,
        epc.code as epcCode,
        bloc.location_code as locationCode
        FROM 
        "local".local_MANIFEST_DATA AS md,
        "local".local_OBJECT_EVENT AS ov,
        "local".local_OBJECT_EVENT_EPCS AS ov_epc,
        "local".local_EPC AS epc,
        "local".local_BUSINESS_LOCATION AS bloc,
        "local".local_OBJECT_EVENT_MANIFEST_DATA AS om
        WHERE
        md.id=om.manifest_id
        AND ov.deleted=0
        AND md.deleted=0
        AND ov.id=ov_epc.object_event_id
        AND ov.id=om.object_event_id
        AND bloc.id=ov.business_location_id
        AND ov_epc.epc_id=epc.id
        GROUP BY om.manifest_id

        UNION
        SELECT 
        trm.manifest_id AS manifest_id,
        trv.id AS eventId,
        'TRANSACTION_EVENT' AS eventType,
        trv.event_time AS eventDate,
        md.manifest_id AS manifestID,
        trv.business_step_code AS businessStepStr,
        md.manifest_version AS manifestVersion,
        md.external_location AS externalLocation,
        md.remark AS remark,
        epc.code as epcCode,
        bloc.location_code as locationCode
        FROM 
        "local".local_MANIFEST_DATA AS md,
        "local".local_TRANSACTION_EVENT AS trv,
        "local".local_TRANSACTION_EVENT_EPCS AS trv_epc,
        "local".local_EPC AS epc,
        "local".local_BUSINESS_LOCATION AS bloc,
        "local".local_TRANSACTION_EVENT_MANIFEST_DATA AS trm
        WHERE
        md.id=trm.manifest_id
        AND trv.deleted=0
        AND md.deleted=0
        AND trv.id=trv_epc.transaction_event_id
        AND trv.id=trm.transaction_event_id
        AND bloc.id=trv.business_location_id
        AND trv_epc.epc_id=epc.id
        GROUP BY trm.manifest_id

下面是使用GROUP和在mysql中查询结果的快照：

使用组查询结果

没有分组的查询结果

T

@fredt..。谢谢你的详细解释。关于你的建议，我已经试过了。但不知怎的，我得到了这个错误：

错误:找不到表: TABL_B在语句中选择TABL_B.* FROM (从TABL_A错误代码中选择DISTINCT manifest_id：-22 )

以下是我的查询：

代码语言：javascript
复制
SELECT TABL_B.* FROM (SELECT DISTINCT manifest_id FROM "local".local_AGGREGATION_EVENT_MANIFEST_DATA) TABL_A
LATERAL JOIN 
( SELECT 
    agm.manifest_id AS manifest_id,
    agv.id AS eventId,
    'AGGREGATION_EVENT' AS eventType,
    agv.event_time AS eventDate,
    md.manifest_id AS manifestID,
    agv.business_step_code AS businessStepStr,
    md.manifest_version AS manifestVersion,
    md.external_location AS externalLocation,
    md.remark AS remark,
    epc.code as epcCode,
    bloc.location_code as locationCode
    FROM 
    "local".local_MANIFEST_DATA AS md,
    "local".local_AGGREGATION_EVENT AS agv,
    "local".local_AGGREGATION_EVENT_EPCS AS agv_epc,
    "local".local_EPC AS epc,
    "local".local_BUSINESS_LOCATION AS bloc,
    "local".local_AGGREGATION_EVENT_MANIFEST_DATA AS agm
    WHERE
    md.id=agm.manifest_id
    AND agv.deleted=0
    AND md.deleted=0
    AND agv.id=agm.aggregation_event_id
    AND agv.id=agv_epc.aggregation_event_id
    AND agv.business_location_id=bloc.id
    AND bloc.id=agv.business_location_id
    AND agv_epc.epc_id=epc.id AND manifest_id = TABL_A.manifest_id  LIMIT 1 ) TABL_B

谢谢@fredt..。我注意到逗号，并已经添加到我的查询。我也试着删除连接词。但仍然抛出同样的错误..。

代码语言：javascript
复制
ERROR: Table not found in statement [SELECT TABL_B.* FROM (SELECT
DISTINCT MANIFEST_ID FROM "local".local_AGGREGATION_EVENT_MANIFEST_DATA)
AS TABL_A, LATERAL] Error Code: -22
hsqldb
sql
view
EN

回答 1

Stack Overflow用户

发布于 2012-07-24 11:24:25

使用HSQLDB2.2.x或更高版本：

使用GROUP BY时，所有选定的列都必须在组按列表中，但作为聚合的任何列除外。在您的示例中，组按列表应该包含11列，而不是一列。

您可以使用DISTINCT，如在SELECT DISTINCT COL1, COLB, COLC, ...中不使用group。DISTINCT是对SELECT列表中的所有列进行分组的快捷方式。

通常，GROUP BY意味着查询应该只为GROUP中的每个列值组合返回一行。当聚合将可能的多行值组合为一个值时，就允许聚合。

现在，如果您按列表包含组中的所有列，并且结果有多个具有相同manifestID值的行，这意味着您不能单独在manifestID上分组。

Update:您使用MySQL查询的结果显示，在这方面不太严格。有超过20行的manifestId=bhbhbhbh，而其他列中的值并不相同。然而，MySQL随机返回一行。这不是GROUP按照其他数据库(包括HSQLDB )支持的SQL标准工作的方式。请参阅MySQL专家的博客：

http://www.mysqlperformanceblog.com/2006/09/06/wrong-group-by-makes-your-queries-fragile/

如果您想要类似于MySQL输出的内容，那么您需要这样的查询：

代码语言：javascript
复制
SELECT TABL_B.* FROM (SELECT DISTINCT manifestID FROM "local".local_AGGREGATION_EVENT_MANIFEST_DATA) TABL_A,
LATERAL 
( [YOUR SELECT STATEMENT WITHOUT GROUP BY] AND manifestID = TABL_A.manifestID  LIMIT 1 ) TABL_B

先尝试视图中的一个选择，然后应用于其余的选择。

您可以通过在限制1之前按COL_NAME添加订单来选择返回哪一行。

票数 2
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/11627814

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档