Thank you @arcange for creating STEEMSQL!
STEEM SQL Tutorial Series:
@dapeng has some posts on "History of Today" which reveals some old posts that were published at the same time (but not this year apparently) on steemit. It can be achieved using the following SQL.
select top 10 *
from
Comments
where
FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and
FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
title <> ''
order by
total_payout_value desc
Let's explain this line by line.
select top 10 *
Choosing the first 10 postsfrom Comments
TableComments
holds posts and commentsFORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us')
The Month and Day should be the same as todayFORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us')
But it can't be this year.title <> ''
Filter out comments, which have empty titles.order by total_payout_value desc
Show the most earned posts first.
We could also add other conditions, such as categories = 'cn'
if we are only interested in the CN posts.
Image Credit: Pixabay.com
感谢 @arcange 创造了 STEEMSQL!
STEEM SQL 系列:
@dapeng 之前搞了一个 “历史上的今天” 挖坟贴 ,也就是通过 SQL 查询 在以前在同一天发表的帖子,听起来很玄乎?实际上就是以下SQL。我们先来看看:
select top 10 *
from
Comments
where
FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us') and
FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us') and
title <> ''
order by
total_payout_value desc
我们来解释一下:
select top 10 *
选择前10条记录from Comments
查询 STEEMSQL 里的Comments
表FORMAT(created,'MM-dd','en-us') = FORMAT(GetDate(),'MM-dd','en-us')
发表时间的月和日要和今天一样FORMAT(created,'yyyy','en-us') <> FORMAT(GetDate(),'yyyy','en-us')
但是又不能是今年。title <> ''
标题为空,也就是限制文章类型(评论的标题一般是空)order by total_payout_value desc
按照收益排序
我们还可以其它条件, 比如 categories = 'cn'
只查询第一个标签为 cn 的帖子。
// Later, it may be reposted to my blogs: justyy.com, helloacm.com and codingforspeed.com 稍后同步到我的中文博客和英文计算机博客。
Originally published at https://steemit.com Thank you for reading my post, feel free to Follow, Upvote, Reply, ReSteem (repost) @justyy which motivates me to create more quality posts.
原文首发于 https://Steemit.com 首发。感谢阅读,如有可能,欢迎Follow, Upvote, Reply, ReSteem (repost) @justyy 激励我创作更多更好的内容。
@justyy 是CN 区的点赞机器人,对优质内容进行点赞,只要代理给 @justyy 每天收利息(100 SP 每天0.04 SBD)并且能获得一次相应至少2倍的点赞,可以认为是VP 200%+ ,详细请看:
欢迎你发表你的见解和看法,特别有意思的评论我可能会奖励你1 SBD哦。
Interesting Comments might be rewarded with 1 SBD.
That is awesome!! great job man!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Thanks!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
我之前也reply过 @dapeng 。我觉得挖出“骨灰贴”还是有意义的。
我也想知道那些我关心的朋友和前辈们,曾经写过什么文章,曾经有过什么想法。
对于还在起步阶段的我,也许是一种启发;对于认识这些朋友们,也可以更丰富、更深入。
所以我觉得这个有继续下去的必要的。也许挖出来的贴并都是我感兴趣的。但是,总有一些精彩,是我错过的!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
走我的路,让我无路可走……所以我不写了……
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
你充分利用R语言的优势,可以生成各种图。
另外再想想还有什么功能他还没做。我也在想。但因为我手头网站没做完,我老公不给我vps空间。
我们俩一直夹击 @justyy哈哈,总有一些是他还没想到的哈哈哈。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
不想了。想了仍然会被揭秘。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
直接做呀,想肯定不行,想完他就会做哈哈
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
走我的路,让我无路可走……
一个魔术师费尽心思,想到一个新魔术,然后立刻被揭秘效仿……归隐山林,从此不再开发新魔术。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
这……眼光要放远一点嘛,你可以继续开发,不矛盾啊
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Nice meme
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
很好的教程。请教个问题:Comments和TxComments有什么区别?Steemsql有没有文档?还是都要自己去猜?
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
没有文档,我也是自己摸索。。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
steemsql是不是mssql?
是不是mssql的手册就可以用呀?
@nationalpark 大哥一问,我有些蒙了。。。
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
your post is just awesome... just love it ...
i just started following you follow me back on @heisali please
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
your post is just awesome... just love it ...
i just started following you follow me back on @heisali please
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
This post recieved an upvote from minnowpond. If you would like to recieve upvotes from minnowpond on all your posts, simply FOLLOW @minnowpond
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Nice work I like this post thanks for sharing this post....
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
好复杂
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Nice post.
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Nice post friend. thanks for sharing
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Interesting. I am wondering between
title <> ''
And
depth = 0
Which one is faster?
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
Thanks. didn't know depth = 0, which is obviously faster..
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
You're welcome. Thanks for confirming :-) I just suspect the numeric compare may be better off.
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
我的能有多少可以代理啊?给我都代理了吧!
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit
根据 https://steemd.com/@xiaoshancun 你可以代理 91 SP,但不推荐,你要不代理 50 SP 先试试?
要的话,点这个: https://v2.steemconnect.com/sign/delegateVestingShares?delegator=xiaoshancun&delegatee=justyy&vesting_shares=50%20SP
Downvoting a post can decrease pending rewards and make it less visible. Common reasons:
Submit