Mercurial > hgrepos > hgweb.cgi > s4
changeset 255:3ae8b70b8395
Add commentary about SQL
author | HIROSE Yuuji <yuuji@gentei.org> |
---|---|
date | Mon, 18 Jul 2016 09:26:12 +0859 |
parents | 8a0cf46afb0a |
children | e9b33033d95f |
files | s4-funcs.sh |
diffstat | 1 files changed, 16 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/s4-funcs.sh Mon Jul 18 00:32:46 2016 +0859 +++ b/s4-funcs.sh Mon Jul 18 09:26:12 2016 +0859 @@ -1511,6 +1511,22 @@ GROUP BY blogid ORDER BY val DESC LIMIT 50 ) m ON b.id=m.blogid;" + # This should be as follows + : <<EOF +WITH arts AS( + SELECT (SELECT rowid FROM blog WHERE id=a.blogid) brid, + a.blogid, a.id id, s.val ctime + FROM article a NATURAL JOIN article_s s + WHERE s.key = 'ctime' AND a.author='$user' + GROUP by s.id +) +SELECT a0.brid,a0.blogid,a0.id,a0.ctime +FROM arts a0 + JOIN + (SELECT blogid,max(ctime) mct FROM arts a1 GROUP BY blogid) a1 + ON a0.blogid=a1.blogid AND a0.ctime=a1.mct +ORDER BY ctime DESC LIMIT 50; +EOF cat<<-EOF <div class="fold">