postgreCompound OR SQL查询(带有with的查询#1)和带有COUNT()exclusions的(查

Question

编辑

是的，你是对的。我在伪代码中寻找的是

（st_area（geom）>0.1）或（COUNT（*）>1）

大写：

返回一个列表，该列表只包含面积大于0.1的州，但如果该州是该国唯一的州（通常是岛国，旁边有足够的标签空间），则不要排除该州。被排除在外的州是像斯洛文尼亚这样有100个省，但面积很小的地方（英国也是罪犯）。

---

我有一个全世界的表格，列出了所有的州和省（我称之为州，但也可以指省）。

StateName、ContryName、Pop、几何

该表位于PostGreSQL 9.2 PostGIS 2.0 上

我需要删除要标记的小州（区域太小）。但如果它是一个岛屿（一个国家，一个州），那么我想把它留在里面。

我的Naive查询是这样的，但有一个语法错误：

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;

这就是语法错误（我预料会发生这种情况）。

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)

Answer 1

两个问题：

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

但使用NOT EXISTS:的反半连接可以更优雅地解决这一问题

SELECT s.name, s.admin, st_area(geom)
FROM   vector.states s
WHERE  st_area(s.geom) > 0.01           -- state big enough ...
   OR NOT EXISTS (                      -- ... or there are no other counties
         SELECT 1 FROM vector.states s2
         WHERE  s2.admin = s.admin
         AND    s2.pk_column <> s.pk_column  -- exclude self-join
         )
ORDER BY s.admin;

将pk_column替换为实际的主键列。

Answer 2

编辑答案，我第一次没有理解问题，对不起

试试这个：

SELECT s.name,s.admin, st_area(geom)
FROM vector.states s
WHERE s.admin in (
SELECT ss.admin
FROM vector.states ss
GROUP BY ss.admin
HAVING (COUNT(*) > 1)
)
AND (st_area(s.geom) > 0.01)
ORDER BY s.admin ASC;

希望它能有所帮助！

Answer 3

这部分：(st_area(ss.geom) > 0.01)不属于HAVING子句。HAVING是基于聚合函数来限制结果，就像COUNT一样，但对于其他函数，必须使用WHERE。

然后，我将使用两个单独的查询来获得这些不同的值：

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