甲骨文有>单排

发布于 2024-12-10 06:06:01 字数 342 浏览 0 评论 0原文

我有一个 Oracle 查询,它的效果是

Have Count(field) > (返回一行的长SQL语句) 查询的双方单独工作,但一起工作时我得到一个“not a group by”表达式。

当用数字替换长 SQL 语句时,它可以工作,但我认为如果只返回一行,两者是等效的?

编辑 经过一番尝试后我意识到: ... 表 T ... 具有计数(字段)> (表 A 的长 SQL 语句,其中 A.field = T.field) 当我用 T.field 的任何特定选项替换 T.field 时,它会起作用,但是当我专门引用 T.field 时,我得到相同的“不是按表达式分组”

I have an Oracle query, which has something to the effect of

Having Count(field) > (Long SQL statement that returns one row)
Both sides of the query work alone, but together I get a "not a group by" expression.

When replacing the long SQL statement with a number it works, but I assumed the two were equivalent if only one row is returned?

Edit
After doing some playing around I realized:
... Table T ... Having Count(field) > (Long SQL statement with Table A Where A.field = T.field)
It works when I replace T.field with any of the specific options for T.field, but when I reference T.field specifically I get the same "not a group by expression"

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(2

卖梦商人 2024-12-17 06:06:01

当 Oracle 解析您的查询时,它不知道查询是只返回一行还是一堆行。因此,只需将 group by your_column 附加到查询末尾即可。

例如,此查询返回一行:

select count(*) from user_objects;

但如果我想同时包含 sysdate,我将不得不这样做

select 
    sysdate the_date, 
    count(*) 
from
    user_objects 
group by 
    the_date;

When Oracle parses your query it doesn't know if the query is going to return only one row or a bunch of rows. So simply append group by your_column to the end of your query.

For example this query returns one row:

select count(*) from user_objects;

But if I wanted to include sysdate along with that, I would have to do

select 
    sysdate the_date, 
    count(*) 
from
    user_objects 
group by 
    the_date;
萌辣 2024-12-17 06:06:01
SELECT ... 
FROM Table T ...
GROUP BY T.afield
HAVING Count(T.anotherfield) 
       > (Long SQL statement with Table A Where A.somefield = T.afield)

应该可以正常工作。


SELECT ... 
FROM Table T ...
GROUP BY T.anotherfield
HAVING Count(T.anotherfield) 
       > (Long SQL statement with Table A WHERE A.somefield = T.afield)

不应该工作。未包含在 GROUP BY 列表中的字段(如 T.afield)不能在 SELECTHAVING< 中引用/code> 或 ORDER BY 子句。只能引用该字段的聚合函数 - 例如,您可以使用 WHERE A.somefield = MIN(T.afield)

SELECT ... 
FROM Table T ...
GROUP BY T.afield
HAVING Count(T.anotherfield) 
       > (Long SQL statement with Table A Where A.somefield = T.afield)

should work ok.


SELECT ... 
FROM Table T ...
GROUP BY T.anotherfield
HAVING Count(T.anotherfield) 
       > (Long SQL statement with Table A WHERE A.somefield = T.afield)

should not work. A field (like T.afield) that is not included in the GROUP BY list, cannot be referenced in SELECT, HAVING or ORDER BY clauses. Only aggregate functions of that field can be referenced - you could have WHERE A.somefield = MIN(T.afield) for example.

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文