我有一张这样的桌子：table1
+----+-----+------+
| id | seq | test |
+----+-----+------+
| 1 | 1 | HR |
| 1 | 2 | RR |
| 2 | 1 | HR |
| 2 | 2 | RR |
| 2 | 3 | OXY |
| 3 | 1 | HR |
| 3 | 2 | RR |
| 4 | 1 | HR |
| 4 | 2 | RR |
| 4 | 3 | OXY |
+----+-----+------+
我想得到如下的结果表。也就是说，只有当一个特定id的所有三个序列号都存在时，我才需要拥有该特定id的所有行：
+----+-----+------+
| id | seq | test |
+----+-----+------+
| 2 | 1 | HR |
| 2 | 2 | RR |
| 2 | 3 | OXY |
| 4 | 1 | HR |
| 4 | 2 | RR |
| 4 | 3 | OXY |
+----+-----+------+
我期待着写一个plpgsql函数给我的解决方案。我对plpgsql和编程还比较陌生。如果有人帮我弄到结果那就太好了。
到目前为止，这是我的函数，它是不完整的：
CREATE OR REPLACE FUNCTION test()
returns SETOF table1 AS $$
DECLARE
cur CURSOR FOR
SELECT *
FROM table1
ORDER by id;
rec_cur RECORD;
counter INTEGER DEFAULT 0;
BEGIN
OPEN cur;
FETCH FIRST FROM cur INTO rec_cur;
MOVE RELATIVE +1 FROM cur;
LOOP
FETCH cur INTO rec_cur;
EXIT WHEN NOT FOUND;
IF rec_cur.seq = 1 AND counter = 0 THEN
RETURN NEXT rec_cursor;
END IF;
END LOOP;
CLOSE cur;
RETURN;
END ; $$
LANGUAGE PLPGSQL STABLE PARALLEL SAFE;
最佳答案
光标绝对不是正确的方法。您可以很容易地使用聚合和having：
select id
from t
where seq in (1, 2, 3)
group by id
having count(seq) = 3;
然后，要获取原始行，有多种方法：
select t.*
from t join
(select id
from t
where seq in (1, 2, 3)
group by id
having count(seq) = 3
) tt
on t.id = tt.id;
编辑：
如果序列号始终从1开始且没有间隙，则可以使用窗口函数：
select t.*
from (select t.*, max(t.seq) over (partition by t.id) as maxseq
from t
) t
where maxseq = 3;