代码之家 › 专栏 › 技术社区 › Piotr Byzia

SQL错误:滥用聚合

sqlite sql

22

Piotr Byzia · 技术社区 · 17 年前

聚合函数有什么问题?此外,我怀疑ORDER BY也不起作用。如何重写这个?

sqlite> SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
   ...> FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2
   ...> WHERE p1.id = i1.PDB_first_id
   ...> AND p2.id = i2.PDB_second_id
   ...> AND i1.id = i2.id
   ...> AND d1>100
   ...> AND d2>100
   ...> ORDER BY d1, d2;
SQL error: misuse of aggregate: 
sqlite>

2 回复 | 直到 17 年前

1

86

Frederik Gheysels 17 年前

使用聚合函数(sum/count/…)时,还必须使用GROUPBY子句。

SELECT p1.domain_id, p2.domain_id, COUNT(p1.domain_id) AS d1, COUNT(p2.domain_id) AS d2
    FROM PDB as p1, Interacting_PDBs as i1, PDB as p2, Interacting_PDBs as i2
    WHERE p1.id = i1.PDB_first_id
    AND p2.id = i2.PDB_second_id
    AND i1.id = i2.id
GROUP BY p1.domain_Id, p2.domain_Id
HAVING d1 > 100 AND d2 > 100
ORDER BY d1, d2;

2

10

mFlorin 9 年前

此问题的简短版本修复程序是:

当你使用函数时,比如 COUNT/SUM ,您需要使用 HAVING 而不是 WHERE

3

0

Atalay K. 5 年前

另外,请确保您中的任何一位都没有在WHERE子句中使用COUNT/SUM列。确保对这些变量使用have而不是WHERE。