First Query
select name, letters, date, count(p.playernumber) as data
from players p inner join fines f using(playernumber)
group by playernumber
Second Query
select name, letters, date, count(p.playernumber) as data
from fines f inner join players p using(spelersnr)
group by playernumber
the first query is not able to run while second query is. I thought the order of inner join didn't matter? How come the second query is able to run because I thought every column in the select (except aggregations) has to be in the group by?
第二个查询是第一个查询无法运行。我认为内连接的顺序无关紧要?为什么第二个查询能够运行,因为我认为select(聚合除外)中的每一列都必须在组中?
1 个解决方案
#1
3
Very interesting. This is due to a Postgres supporting using primary and unique keys in the group by clause and then allowing columns associated with each key to be in the select with no aggregation functions. The ANSI standard supports this.
很有意思。这是因为Postgres支持在group by子句中使用主键和唯一键,然后允许与每个键关联的列在没有聚合函数的select中。 ANSI标准支持这一点。
The issue is that spelersnr is ambiguous -- it could come from either table. I guess Postgres defaults to the first table for parsing the query. When the column is the primary key, then it is fine (because naam etc. come from that table). When the column is not a primary key, then it you get the error.
问题是spelersnr含糊不清 - 它可能来自任何一个表。我猜Postgres默认使用第一个表来解析查询。当列是主键时,它很好(因为naam等来自该表)。当列不是主键时,则会出现错误。
The solution is simply:
解决方案很简单:
group by s.spelersnr
I would expect Postgres to issue an error like "spelersnr is ambiguous" -- meaning it doesn't know what table it is coming from. If you used an on clause instead of a using clause, you should get such an error for both queries.
我希望Postgres会发出一个错误,例如“spelersnr is ambiguous” - 这意味着它不知道它来自哪个表。如果使用on子句而不是using子句,则两个查询都会出现这样的错误。
#1
3
Very interesting. This is due to a Postgres supporting using primary and unique keys in the group by clause and then allowing columns associated with each key to be in the select with no aggregation functions. The ANSI standard supports this.
很有意思。这是因为Postgres支持在group by子句中使用主键和唯一键,然后允许与每个键关联的列在没有聚合函数的select中。 ANSI标准支持这一点。
The issue is that spelersnr is ambiguous -- it could come from either table. I guess Postgres defaults to the first table for parsing the query. When the column is the primary key, then it is fine (because naam etc. come from that table). When the column is not a primary key, then it you get the error.
问题是spelersnr含糊不清 - 它可能来自任何一个表。我猜Postgres默认使用第一个表来解析查询。当列是主键时,它很好(因为naam等来自该表)。当列不是主键时,则会出现错误。
The solution is simply:
解决方案很简单:
group by s.spelersnr
I would expect Postgres to issue an error like "spelersnr is ambiguous" -- meaning it doesn't know what table it is coming from. If you used an on clause instead of a using clause, you should get such an error for both queries.
我希望Postgres会发出一个错误,例如“spelersnr is ambiguous” - 这意味着它不知道它来自哪个表。如果使用on子句而不是using子句,则两个查询都会出现这样的错误。