本文介绍了SQL 错误:滥用聚合的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
SQLite 3.4.0 版聚合函数有什么问题?此外,我怀疑 ORDER BY 也不会起作用.如何重写这个?
SQLite version 3.4.0 What's wrong with aggregate functions? Additionally, I suspect that ORDER BY won't work as well. How to rewrite this?
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> 推荐答案在使用聚合函数(sum/count/... )时,您还必须使用 GROUP BY 子句.
When using an aggregate function (sum / count / ... ), you also have to make use of the GROUP BY clause.
接下来,当你想过滤一个聚合的结果时,你不能在 WHERE 子句中这样做,但你必须在 HAVING 子句中这样做.
Next to that, when you want to filter on the result of an aggregate , you cannot do that in the WHERE clause, but you have to do that in the HAVING clause.
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;更多推荐
SQL 错误:滥用聚合
发布评论