H2 DB-列必须在“分组依据”列表中 [英] H2 DB - Column must be in Group By list

问题描述

我正在使用H2-DB访问静态数据库...

i am using H2-DB to access static databases...

我有一个看起来像这样的表：

i have a table which looks like:

COUNTRY     STATE       CITY         LAT     LNG     COUNTRYID      STATEID     CITYID 
"Germany"   "Berlin"    ""           1.23    1.23    1              1           0
"Germany"   "München"   ""           1.23    1.23    1              2           0
"USA"       "Alabama"   "Auburn"     1.23    1.23    2              1           1
"USA"       "Alabama"   "Birmingham" 1.23    1.23    2              1           2
"USA"       "Alaska"    "Anchorage"  1.23    1.23    2              2           1
"USA"       "Alaska"    "Cordova"    1.23    1.23    2              2           2

包含很多国家/地区，这是一个庞大的清单，其中大多数只有国家/地区和州（例如德国，而州是城市），也有一些城市（如美国）。

its a huge list with lots of countries, most of them just have Country and State (like Germany here, whereas State's are Cities), a few also have a City (like USA here)...

现在的问题是，当我查询

the problem is now, when i query

SELECT * FROM MyTable WHERE COUNTRY = 'Germany' group by STATE order by STATE

要获取州（或城市）的排序列表，我会收到一条错误消息

to get a sorted list of the states (or cities), i get an error saying

Field CITY must be in the GROUP BY list

如果行中有城市，我需要整行，否则我只需要State列，但是我可以在查询之后知道它是否使用city列，因此我必须查询 *而不是 STATE

if the row has a city, i need the whole row, otherwise i would only need the State column, but i can just know after having queried it, wether it uses the city column or not, so i have to query "*" instead of "STATE"

查询应该可以，还是？在MySql上它可以正常工作...所以这里出了什么问题？

the query should be okay, or? On MySql it is properly working... so whats the problem here?

如果有帮助，发现了这个问题： http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90016

Found this if it helps: http://www.h2database.com/javadoc/org/h2/constant/ErrorCode.html#c90016

Metin

推荐答案

MySQL在此方面已被破坏。它允许 GROUP BY 中的列既不在 group by 中，也不在聚合函数的参数中。实际上，文档警告不要使用此功能。扩展名。

MySQL is broken in regards to this. It allows columns in the GROUP BY that are neither in the group by nor arguments to aggregation functions. In fact, the documentation warns against using this extension.

因此您可以这样做：

SELECT state
FROM DIYANET
WHERE COUNTRY = 'Germany'
GROUP BY STATE 
ORDER BY STATE;

或类似这样的东西：

SELECT state, min(city), min(lat), . . .
FROM DIYANET
WHERE COUNTRY = 'Germany'
GROUP BY STATE 
ORDER BY STATE;

但是 SELECT * 是不允许的，并且不会真的没有道理。

But SELECT * is not allowed and doesn't really make sense.

这篇关于H2 DB-列必须在“分组依据”列表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！