SteemSQL 教程 - 一鸣惊人的作者 | 小赖子的英国生活和资讯

@nationalpark 兄在他的帖子里列出了史上一鸣惊人的作者, 也就是只发表一篇文章但收入却好多金.

通过 SteemSQL 的 Comments 表格, 我们可以通过下面的条件把评论给去除掉:

depth = 0

然后只需要用 group author 来对作者进行归类, 通过 having count(1) = 1 对作者的文章数进行限制. 如果是归类的字段, 我们需要用 having 而不是 where

然后就是排序了, 你可以用 order by max(total_pending_payout) desc 对收益进行从大到小的排序. 因为只有一篇, 你可以用 max, min 或者是 sum 都可以.

最终版本是:

select 
    author, 
    max(total_payout_value) "payout"
from
    comments (NOLOCK)       
where
    depth = 0
group by
    author
having
    count(1) = 1
order by
    max(total_payout_value) desc

select 
    author, 
    max(total_payout_value) "payout"
from
    comments (NOLOCK)       
where
    depth = 0
group by
    author
having
    count(1) = 1
order by
    max(total_payout_value) desc

max(total_payout_value) 有点重复了, 在 MYSQL 里可以定义别名直接用, 但很可惜在 MSSQL 里却不行, 不过我们可以换成嵌套的SQL, 重写一下:

select T.author, T.payout 
from (
    select 
        author, 
        max(total_payout_value) "payout", 
        count(1) "count"
    from
        comments (NOLOCK)       
    where
        depth = 0
    group by
        author
) T
where
    T.count = 1
order by
    T.payout desc

select T.author, T.payout 
from (
    select 
        author, 
        max(total_payout_value) "payout", 
        count(1) "count"
    from
        comments (NOLOCK)       
    where
        depth = 0
    group by
        author
) T
where
    T.count = 1
order by
    T.payout desc

两种写法都返回了一样的数据: