字段必须出现在GROUP BY子句中还是用于聚合函数？ [英] field must appear in the GROUP BY clause or be used in an aggregatefunction?
问题描述
嘿所有。
我遇到了一个我有点神秘的SQL问题。关于这个问题我有两个不同的问题：为什么？以及如何解决这个问题？
以下查询：
SELECT GCP.id，
GCP .Name
FROM Gov_Capital_Project GCP，
WHERE TLM.TLI_ID = $ 2
group by GCP.id
ORDER BY gcp.name;
产生以下错误：
错误：列" gcp.name"必须出现在GROUP BY子句中或用于聚合函数
该字段是CHAR，所以我不确定要使用哪种聚合，
或（对我的理解更重要）为什么有必要。
正如我所说，我不确定我理解为什么会这样。我假设我不理解" group by以及我以为我做过;）
这不是我的疑问，我正在将MSSQL中的原型系统翻译成
Postgres。此查询_does_在MSSQL中工作。这是否构成了MSSQL中的一个错误，或者是Postgres的一个缺点，或者仅仅是
解释的差异？
- -
Bill Moran
潜在技术
http://www.potentialtech.com
---------------------------（广播结束）---------------------------
提示9：计划者会忽略你的选择欲望你的
加入列的数据类型不匹配的索引扫描
Hey all.
I''ve hit an SQL problem that I''m a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?
The following query:
SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Project GCP,
WHERE TLM.TLI_ID = $2
group by GCP.id
ORDER BY gcp.name;
Produces the following error:
ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an aggregate function
That field is a CHAR, so I''m not sure what kind of aggregate to use,
or (more important to my understanding) why one is necessary.
As I said, I''m not sure I understand why this occurs. I''m assuming that I
don''t understand "group by" as well as I thought I did ;)
This isn''t my query, I''m translating a system prototyped in MSSQL to
Postgres. This query _does_ work in MSSQL. Does that constitute a
bug in MSSQL, or a shortcomming of Postgres, or just a difference of
interpretation?
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
推荐答案
2
group by GCP.id
ORDER BY gcp.name;
产生以下错误：
错误：列gcp.name必须出现在GROUP BY子句中或用于聚合函数
该字段是CHAR，所以我不确定要使用哪种聚合，
或（对我的理解更重要）为什么有必要。
正如我所说，我不确定我理解为什么会这样。我假设我不理解" group by以及我以为我做过;）
这不是我的疑问，我正在将MSSQL中的原型系统翻译成
Postgres。此查询_does_在MSSQL中工作。这是否构成了MSSQL中的一个错误，或者是Postgres的一个缺点，或者仅仅是
解释的差异？
- -
Bill Moran
潜在技术
http://www.potentialtech.com
---------------------------（广播结束）---------------------------
提示9：计划者会忽略你的选择欲望如果您的
加入列的数据类型不匹配，则索引扫描
2
group by GCP.id
ORDER BY gcp.name;
Produces the following error:
ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in an aggregate function
That field is a CHAR, so I''m not sure what kind of aggregate to use,
or (more important to my understanding) why one is necessary.
As I said, I''m not sure I understand why this occurs. I''m assuming that I
don''t understand "group by" as well as I thought I did ;)
This isn''t my query, I''m translating a system prototyped in MSSQL to
Postgres. This query _does_ work in MSSQL. Does that constitute a
bug in MSSQL, or a shortcomming of Postgres, or just a difference of
interpretation?
--
Bill Moran
Potential Technologies
http://www.potentialtech.com
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column''s datatypes do not match
Bill Moran说：
Bill Moran said:
我遇到了一个我有点神秘的SQL问题。关于这个问题我有两个不同的问题：为什么？以及如何解决这个问题？
以下查询：
SELECT GCP.id，
GCP.Name
来自Gov_Capital_Project GCP，
WHERE TLM.TLI_ID =
I''ve hit an SQL problem that I''m a bit mystified by. I have two different
questions regarding this problem: why? and how do I work around it?
The following query:
SELECT GCP.id,
GCP.Name
FROM Gov_Capital_Project GCP,
WHERE TLM.TLI_ID =
2
group by GCP.id
ORDER BY gcp.name;
产生以下错误：
错误：列gcp.name必须出现在GROUP BY子句中或用于聚合函数
2
group by GCP.id
ORDER BY gcp.name;
Produces the following error:
ERROR: column "gcp.name" must appear in the GROUP BY clause or be used in
an aggregate function
因为你没有收集数据，所以你不能使用选择distinct而不是？
选择不同的GCP.id，GCP.Name
来自Gov_Capital_Project GCP，{？这里缺少什么？}
WHERE TLM.TLI_ID =
Since you''re not agregating data, can''t you use a select distinct instead?
SELECT distinct GCP.id, GCP.Name
FROM Gov_Capital_Project GCP, {?something missing here?}
WHERE TLM.TLI_ID =
这篇关于字段必须出现在GROUP BY子句中还是用于聚合函数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
