pgsql 分组查询方法
-
group by
group by 查询的字段 必须出现在分组中。因此这里用 max 函数，来查其他字段
SELECT
"max"("name") as "name",
"max"("organization_id") as "organizationId",
account
FROM
system_users
GROUP BY account
- PARTITION BY
Partition By是分组但不聚合
是按 account 进行分组且组内按 create_time 进行升序，返回组内行编号
rownum = 1 可以实现去重的效果
WITH res AS (
SELECT
organization_id,
"name",
account,
ROW_NUMBER() OVER(PARTITION BY "account" ORDER BY "create_time" ASC nulls last) AS rownum
FROM
system_users
)
SELECT * FROM res WHERE rownum = 1