postgresql 常用SQL整理

1. 删除id最小的重复行

DELETE FROM mes.stool_po where id in(
    SELECT MIN(id) FROM mes.stool_po GROUP BY purchasing_doc_number, purchasing_doc_item_number HAVING COUNT(id) > 1
)

2. 查询根据purchasing_doc_number, purchasing_doc_item_number分组后，重复的数据id

SELECT array_agg(id) FROM mes.stool_po GROUP BY purchasing_doc_number, purchasing_doc_item_number HAVING COUNT(id) > 1;