sql - 如何在没有子查询的情况下依赖 DISTINCT ON

Question

我正在尝试实现分页，它会获取带有尚未验证的特定电子邮件的最后一个配置文件。我不明白为什么此查询失败并显示

column "profile.email" must appear in the GROUP BY clause or be used in an aggregate function

select distinct ON (email) email, count(*) 
from "profile" where "verified" = false 
order by "email" asc, "created_date" asc limit 1

但是这个有效

select distinct ON (email) email, * 
from "profile" where "verified" = false 
order by "email" asc, "created_date" asc limit 10

有什么办法可以不使用子查询来解决这个问题吗？

编辑

经过一些测试，给出的答案似乎不正确。

select distinct ON (email) email, count(*) over()
from "profile" 
where "email" = 'example@test.com' 
order by "email" desc, "created_date" desc limit 1

返回(不正确)

[{email: 'example@test.com',count:18 }]

select distinct ON (email) email
from "profile" 
where "email" = 'example@test.com' 
order by "email" desc, "created_date" desc limit 10

返回(正确)

[{email: 'example@test.com'}]

Answer 1

您可以使用窗口函数:

select distinct ON (email) email, count(*) over ()
from "profile"
where "verified" = false 
order by "email" asc, "created_date" asc
limit 1

对于这个例子，我不知道它有什么优势:

select email, count(*) over ()
from "profile"
where "verified" = false 
order by "created_date" asc
limit 1