如何在firebird中使用GROUP BY [英] How to use GROUP BY in firebird

问题描述

T_TABLE2 的结构是

  ID INT 
 TBL1_ID INT 
 TESTER VARCHAR 
 LOT_ID VARCHAR 
 GRP VARCHAR 
 SITE_NUM INT 
 TEST_NUM VARCHAR 
 TEST_DESC VARCHAR 
测量双精度
单位VARCHAR 
 LL DOUBLE PRECISION 
 UL双精度
 STATUS VARCHAR 
  

我使用SQL编辑器在火鸟测试我的查询。 Th查询是

pre $ SELECT TEST_DESC，MEASUREMENT，LL，UL
FROM T_TABLE2
GROUP BY TEST_DESC


但是我得到了这个错误。

 无效标记。 
动态SQL错误。 
 SQL错误代码= -104。 
选择列表中的表达式无效（不包含在聚合函数或GROUP BY子句中）。 
  

解决方案

您必须来自MySQL。 MySQL - 恕我直言误导，不正确，并以黑魔法，不可预知的方式 - 允许您指定部分 GROUP BY 查询和数据库引擎试图找出查询其余部分哪个所需的非分组列的值。另一方面，标准SQL（Firebird和大多数其他RDBMS）不会;它要求任何非集合列包含在组中，并且任何非组列列都明确指定所需的行。

在你的情况中，违规的列是 MEASUREMENT ， LL 和 UL 。您需要指定 MEASUREMENT ， LL 和 UL 你想（是的，即使它们都是一样的;数据库引擎无法知道或保证这一点），或者如果你想分组一个或多个列或可能忘记汇总（您是否想要 SUM ？）

---

的有效查询：

1. 按所有列分组（相当于 SELECT DISTINCT


     SELECT TEST_DESC，MEASUREMENT，LL，UL 
    FROM T_TABLE2 
    GROUP BY TEST_DESC， MEASUREMENT，LL，UL 
     




2. 通过 MEASUREMENT ，并返回 MIN LL和 MAX UL：
   
   

     SELECT TEST_DESC，MEASUREMENT，MIN（LL），MAX（UL）
    FROM T_TABLE2 
    GROUP BY TEST_DESC，MEASUREMENT 
     $ <$> code> $ <$ $ $ $>未分组的列：
     SELECT TEST_DESC，SUM（MEASUREMENT），SUM（LL），SUM（UL）
    FROM T_TABLE2 
    GROUP BY TEST_DESC 
     
    li> 
    
    
   汇总组合： 
    
    
     SELECT TEST_DESC，COUNT（DISTINCT MEASUREMENT）， SUM（LL），MAX（UL）
    FROM T_TABLE2 
    GROUP BY TEST_DESC 
     
    
    
    
    
   The structure of T_TABLE2 is
   ID INT
   TBL1_ID INT
   TESTER VARCHAR
   LOT_ID VARCHAR
   GRP VARCHAR
   SITE_NUM INT
   TEST_NUM VARCHAR
   TEST_DESC VARCHAR
   MEASUREMENT DOUBLE PRECISION
   UNIT VARCHAR
   LL DOUBLE PRECISION
   UL DOUBLE PRECISION
   STATUS VARCHAR
   and I use SQL editor in firebird the test my query. Th query is 
   SELECT TEST_DESC, MEASUREMENT, LL, UL 
   FROM T_TABLE2 
   GROUP BY TEST_DESC
   but I got this error in group by.
   Invalid token.
   Dynamic SQL Error.
   SQL error code = -104.
   Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).
   
   解决方案
   You must be coming from MySQL. MySQL - IMHO misleadingly, incorrectly, and in a black-magicky, unpredictible sort of way - allows you to specify partial GROUP BY queries and the database engine tries to figure out from the rest of the query which value of the non-grouped-by columns you want. Standard SQL (Firebird and most other RDBMSes), on the other hand, does not; it requires any non-aggregate columns to be contained in the group by, and any non-group-by columns to explicitly specify which row you want.
   
   In your case, the offending columns are MEASUREMENT, LL, and UL. You need to specify which MEASUREMENT, LL, and UL you want (yes, even if they are all the same; the database engine has no way of knowing or guaranteeing this), or if you want to group by one or more of the columns or possibly you forgot to aggregate (Did you want the SUM?) 
   
   
   
   Examples of valid queries:
   
   Group by all columns (equivalent to a SELECT DISTINCT):
   SELECT TEST_DESC, MEASUREMENT, LL, UL
   FROM T_TABLE2
   GROUP BY TEST_DESC, MEASUREMENT, LL, UL
   
   Group by MEASUREMENT as well and return the MIN LL and MAX UL:
   SELECT TEST_DESC, MEASUREMENT, MIN(LL), MAX(UL)
   FROM T_TABLE2
   GROUP BY TEST_DESC, MEASUREMENT
   
   SUM non-grouped columns:
   SELECT TEST_DESC, SUM(MEASUREMENT), SUM(LL), SUM(UL)
   FROM T_TABLE2
   GROUP BY TEST_DESC
   
   A combination of aggregates:
   SELECT TEST_DESC, COUNT(DISTINCT MEASUREMENT), SUM(LL), MAX(UL)
   FROM T_TABLE2
   GROUP BY TEST_DESC
   
   
   
                           
   这篇关于如何在firebird中使用GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！