SQL用于查找组中的重复外键

With tables basically like this:

表格基本上是这样的:

Elements
  id INT PRIMARY KEY
  ...

Observations
  id INT PRIMARY KEY
  ...

Data
  id INT PRIMARY KEY
  observation_id FOREIGN KEY
  element_id FOREIGN KEY
  value FLOAT
  ...

I want to find all observation_ids where there are duplicate element_ids in a single observation_id. For example, if I have Data records like:

我想找到所有observe_ids,其中一个observe_id中存在重复的element_id。例如,如果我有数据记录,如:

1|50|23|4.5
2|50|24|9.9
3|66|23|4.4
4|66|23|4.1

Then the query would report observation_id 66 because it has two associated rows with element_id 23.

然后查询将报告observation_id 66,因为它有两个关联的行,其中element_id为23。

(I'm using PostgreSQL, but this is probably a basic SQL question.)

(我正在使用PostgreSQL,但这可能是一个基本的SQL问题。)

1 个解决方案

#1

Use the count() aggregate combined with a 'having' clause:

使用count()聚合和'having'子句:

select observation_id, element_id, count(*)
from Data
group by observation_id, element_id
having count(*) > 1