sqlite SQL错误：误用聚合

cl25kdpy 于 2022-11-15 发布在 SQLite

关注(0)|答案(3)|浏览(152)

SQLite版本3.4.0聚合函数有什么问题？此外，我怀疑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>

sqlite

来源：https://stackoverflow.com/questions/648083/sql-error-misuse-of-aggregate

3条答案

按热度按时间

5m1hhzi41#

在使用聚合函数(SUM/COUNT/...)时，还必须使用GROUP BY子句。
其次，当您想要对聚合结果进行筛选时，您不能在WHERE子句中这样做，但必须在HAVING子句中这样做。

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;

赞(0）回复(0）举报 2022-11-15

wi3ka0sx2#

针对此问题的简短版本修复为：
当您使用像COUNT/SUM这样的函数时，您需要使用HAVING而不是WHERE。

赞(0）回复(0）举报 2022-11-15

j9per5c43#

另外，请确保没有将您正在使用的COUNT/SUM列之一用于WHERE子句。确保对这些变量使用HAVING而不是WHERE。

赞(0）回复(0）举报 2022-11-15