SQL错误:滥用聚合

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>

Frederik Ghe.. 71

使用聚合函数(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;

+1我想在没有合适的GROUP BY子句的情况下使用聚合函数的根源在于MySQL带来的坏习惯...... (3认同)

---

小智.. 5

对此的短版修复是：

当您使用like之类的功能时COUNT/SUM，您需要使用HAVING而不是WHERE。

1> Frederik Ghe..：

---

使用聚合函数(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;

---

+1我想在没有合适的GROUP BY子句的情况下使用聚合函数的根源在于MySQL带来的坏习惯......

2> 小智..：

---

对此的短版修复是：

当您使用like之类的功能时COUNT/SUM，您需要使用HAVING而不是WHERE。