有个数据表 userlog 如下:
需要查询组装成如下表格:
1
halfcrazy 2016-03-23 13:10:32 +08:00
select user, count(*) as cnt, logintime from userlog order by cnt desc, logintime asc group by user
|
2
idblife 2016-03-23 13:17:16 +08:00
select user, count(*) ,min(logintime),max(logintime) from userlog
|
3
jhdxr 2016-03-23 13:21:23 +08:00
直接 select *,登录次数自己写代码统计即可(一个 string-int 的 map , key 是用户, value 是次数)
|
4
klesh 2016-03-23 13:31:27 +08:00 1
目测一楼的 sql 不行
select user group by user 是可以,其它的字段必须是 aggregate function. select logintime 是不行的。另外 order 需要在 group 后面 1.分两次查询 SELECT user, count(*) AS cnt FROM userlog GROUP BY user ORDER BY cnt; SELECT logintime FROM userlog WHERE user=? ORDER BY logintime; 2.利用 GROUP_CONCAT ( mysql 用得少,未实测,不保证书写正确) SELECT user, count(*) AS cnt, GROUP_CONCAT(logintime SEPARATOR '\n' ORDER BY logintime) AS logintimes FROM userlog GROUP BY user ORDER BY cnt; |