代码之家 › 专栏 › 技术社区 › AndreaNobili

为什么此GROUP BY子句会导致此错误“选择列表的42000表达式#1不在GROUP BY子句中,并且包含未聚合的列…”?

rdbms group-by database sql mysql

AndreaNobili · 技术社区 · 6 年前

我正在努力 MySql 数据库和我正在实现此查询:

SELECT
    LS.id                                           AS livestock_species_id,
    LS.parent_livestock_species_id                  AS parent_livestock_species_id,
    LS.livestock_species_name_en                    AS livestock_species_name_en,  
    LSN.livestock_species_name                      AS livestock_species_name,
    LSN.description                                 AS description,
    LS.image_link                                   AS image_link

FROM LivestockDetails                               AS LD
INNER JOIN LivestockSpecies                         AS LS
      ON LD.live_stock_species_id = LS.id
LEFT JOIN LivestockSpeciesName                     AS LSN
      ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3          

WHERE
     LD.ls_vaccination_id is not null

上一个查询返回以下结果集:

livestock_species_id parent_livestock_species_id livestock_species_name_en                          livestock_species_name                             description image_link                                                                                                                                                                                                                                                     
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1                                                Cow                                                Inka                                               Inka        https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c                                                                                
1                                                Cow                                                Inka                                               Inka        https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c                                                                                
1                                                Cow                                                Inka                                               Inka        https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c                                                                                
1                                                Cow                                                Inka                                               Inka        https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c                                                                                
2                                                Chicken                                            Inkoko                                             Inkoko      https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221                                                                            
2                                                Chicken                                            Inkoko                                             Inkoko      https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221                                                                            
1                                                Cow                                                Inka                                               Inka        https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c                                                                                
1                                                Cow                                                Inka                                               Inka        https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fcow.png?alt=media&token=c21866df-448a-4a72-9da2-d55d87f8b31c                                                                                
2                                                Chicken                                            Inkoko                                             Inkoko      https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221                                                                            
2                                                Chicken                                            Inkoko                                             Inkoko      https://firebasestorage.googleapis.com/v0/b/MY-PROJECT.appspot.com/o/img%2Ficons%2Flivestock%2Fchicken.png?alt=media&token=badd0660-48ca-49f8-914a-8a9b8574a221

正如你所看到的,返回的记录是重复的,我不需要这些重复。

所以我的想法是使用 分组依据 条款

我的问题是,尝试这样做(按 牲畜\u物种\u id 已检索字段):

SELECT
    LS.id                                           AS livestock_species_id,
    LS.parent_livestock_species_id                  AS parent_livestock_species_id,
    LS.livestock_species_name_en                    AS livestock_species_name_en,  
    LSN.livestock_species_name                      AS livestock_species_name,
    LSN.description                                 AS description,
    LS.image_link                                   AS image_link

FROM LivestockDetails                               AS LD
INNER JOIN LivestockSpecies                         AS LS
      ON LD.live_stock_species_id = LS.id
LEFT JOIN LivestockSpeciesName                     AS LSN
      ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3          

WHERE
     LD.ls_vaccination_id is not null  

GROUP BY 
      livestock_species_id

MySql返回以下错误消息:

42000 SELECT list的表达式#1不在GROUP BY子句中,并且包含未聚合的列“digital\u services\u DB”。LS。id’,它在功能上不依赖于GROUP BY子句中的列;这与sql\u mode=only\u full\u group\u by不兼容

为什么?怎么了?我错过了什么?如何解决这种情况,避免重复?

2 回复 | 直到 6 年前

nacho 6 年前

您需要指定group by(或聚合)中的所有列。在您的情况下,由于您有重复的记录,您可以这样做:

GROUP BY livestock_species_id,parent_livestock_species_id,
    livestock_species_name_en, livestock_species_name, 
    description,image_link

也可以使用distinct:

SELECT DISTINCT
    LS.id                                           AS livestock_species_id,
    LS.parent_livestock_species_id                  AS parent_livestock_species_id,
    LS.livestock_species_name_en                    AS livestock_species_name_en,  
    LSN.livestock_species_name                      AS livestock_species_name,
    LSN.description                                 AS description,
    LS.image_link                                   AS image_link

Damien_The_Unbeliever 6 年前

关于我在re中的评论:首先要防止重复出现-我猜中有多行 LivestockDetails 与同一物种有关。然而,在查询中,您并没有访问该表中的任何数据。

如果您只想报告中至少有一行的物种 LivestockDetails ,使用 EXISTS 改为检查:

SELECT
    LS.id                                           AS livestock_species_id,
    LS.parent_livestock_species_id                  AS parent_livestock_species_id,
    LS.livestock_species_name_en                    AS livestock_species_name_en,  
    LSN.livestock_species_name                      AS livestock_species_name,
    LSN.description                                 AS description,
    LS.image_link                                   AS image_link

FROM LivestockSpecies                         AS LS
LEFT JOIN LivestockSpeciesName                     AS LSN
      ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3          
WHERE
     EXISTS (SELECT * from LivestockDetails LD
             WHERE LD.live_stock_species_id = LS.id
             and LD.ls_vaccination_id is not null)

这应该会产生更好的结果(如果优化器做得很好),因为我们首先不会生成重复项。

(如果exists检查的相关子查询工作不正常,您可能还需要尝试:

SELECT
    LS.id                                           AS livestock_species_id,
    LS.parent_livestock_species_id                  AS parent_livestock_species_id,
    LS.livestock_species_name_en                    AS livestock_species_name_en,  
    LSN.livestock_species_name                      AS livestock_species_name,
    LSN.description                                 AS description,
    LS.image_link                                   AS image_link

FROM (SELECT DISTINCT live_stock_species_id
      FROM LivestockDetails
      WHERE ls_vaccination_id is not null)          AS LD
INNER JOIN LivestockSpecies                         AS LS
      ON LD.live_stock_species_id = LS.id
LEFT JOIN LivestockSpeciesName                     AS LSN
      ON LSN.livestock_species_id = LS.id AND LSN.language_id = 3

至少可以尽早停止复制)