复合OR SQL查询(使用WITH的查询#1)和使用COUNT()排除项的(查询#2) [英] Compound OR SQL query (query #1 with WITH) and (query #2) with COUNT() excusions

问题描述

编辑

是的，您是正确的.我在伪代码中寻找的是一个列表，

Yes, you are correct. What I am looking for in pseudo code is a list that

(st_area(geom)> 0.1)或(COUNT(*)> 1)

(st_area(geom)>0.1) OR (COUNT(*) > 1)

和文字:

返回一个列表，该列表仅包含面积大于0.1的州，但如果它是该国家/地区中唯一的州(通常是岛国，并且旁边有足够的标签空间，则不排除该州)它).被排除在外的州是斯洛文尼亚等有100个省的地方，但土地面积很小(英国也是罪犯).

return a list, that only has only states that have an area greater than 0.1, but don't exclude that state if it is the only one in the country (usually and island country, which has plenty room for labels next to it). The excluded states are places like Slovenia that has 100 provinces, but a tiny land area (Great Britain is also and offender).

我有一张表格，其中列出了所有州和省(我称该表为州，但也可以表示省).

I have a table for the entire world listing all states and provinces (I call the table states, but it also can mean province).

StateName，ContryName，Pop，几何图形

StateName, ContryName, Pop, geometry

该表位于PostGreSQL 9.2 PostGIS 2.0上

The table is on PostGreSQL 9.2 PostGIS 2.0

我需要删除小的状态(区域太小)以进行标记.但是，如果它是一个岛(一个国家，一个州)，那么我想把它留在里面.

I need to remove small states (area too small) to label. But if it is an island (one country, one state) then I want to leave it in.

我的天真查询是这样的，但是存在语法错误:

My Naive query is like this, but there is a syntax error:

SELECT s.name,s.admin, st_area(geom)
FROM vector.states s
INNER JOIN (
SELECT ss.admin
FROM vector.states ss
GROUP BY ss.admin
HAVING (COUNT(*) > 1) AND (st_area(ss.geom) > 0.01)
) a ON a.admin = s.admin
ORDER BY s.admin ASC;

这是语法错误(我希望这会发生).

this is the syntax error (and I expected this to happen).

ERROR:  column "ss.geom" must appear in the GROUP BY clause or be used in an aggregate function
LINE 7: HAVING (COUNT(*) > 1) AND (st_area(ss.geom) > 0.01)

推荐答案

两个问题:

1. 就像错误消息告诉您的一样，如果在GROUP BY中未列出geom，则需要将其包装在聚合函数中.您可以只使用min() ...
2. 您的逻辑倒退了.它必须是COUNT(*) = 1 OR ..

1. Just like the error message tells you, geom needs to be wrapped in an aggregate function if it is not listed in GROUP BY. You could just use min() ...
2. You got your logic backwards. It needs to be COUNT(*) = 1 OR ..

但是使用用您的实际主键列替换pk_column.

Replace pk_column with your actual primary key column(s).

这篇关于复合OR SQL查询(使用WITH的查询#1)和使用COUNT()排除项的(查询#2)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！