在mysql中按相同日期分组
在mysql中按相同日期分组
提问于 2018-06-06 19:14:21
我有一个包含以下字段的userRegister表：
**userRegister table:**
id | name | created | login | logout | userId
------------------------------------------------------------------------
1 |test | 2018-02-19 11:34:00 | 2018-02-19 11:34:00 | NULL | 1
2 |test | 2018-02-21 14:01:42 | 2018-02-21 14:01:42 | NULL | 1
3 |test | 2018-02-21 15:24:05 | 2018-02-21 15:24:05 | NULL | 1
4 |test | 2018-02-22 16:46:15 | 2018-02-22 16:46:15 | NULL | 1
5 |test2 | 2018-02-27 09:51:19 | 2018-02-27 09:51:19 | NULL | 2
6 |test2 | 2018-02-28 11:59:24 | 2018-02-28 11:59:24 | NULL | 2
7 |test |2018-03-01 10:37:59 | 2018-03-01 10:37:59 | NULL | 1
8 |test |2018-03-01 10:39:52 | 2018-03-01 10:39:52 | NULL | 1
9 |test |2018-03-01 10:41:49 | 2018-03-01 10:41:49 | NULL | 1
10 |test |2018-03-01 15:34:52 | 2018-03-01 15:34:52 | NULL | 1在这里，我需要检查用户是否在同一天多次登录而没有注销，而不是使用以前的登录时间更新logout字段
如何编写按创建日期对用户进行分组查询，以便在不注销的情况下多次登录
我尝试过的代码：
db.query("select * from userRegister where logout is NULL limit 10", function (err, user) {
if (!_.size(user)){
console.log([])
}
else{
var result=_.chain(user).groupBy("created").map(function(v, i) {
return {
created: i,
login: _.map(v, 'login'),
userId: _.map(v, 'userId')
}
}).value()
}
})输出：
[ { created: '2018-02-19 11:34:00',
login: [ '2018-02-19 11:34:00' ],
userId: [ '1' ] },
{ created: '2018-02-21 14:01:42',
login: [ '2018-02-21 14:01:42' ],
userId: [ '1' ] },
{ created: '2018-02-21 15:24:05',
login: [ '2018-02-21 15:24:05' ],
userId: [ '1' ] },
{ created: '2018-02-22 16:46:15',
login: [ '2018-02-22 16:46:15' ],
userId: [ '1' ] },
{ created: '2018-02-27 09:51:19',
login: [ '2018-02-27 09:51:19' ],
userId: [ '2' ] },
{ created: '2018-02-28 11:59:24',
login: [ '2018-02-28 11:59:24' ],
userId: [ '2' ] },
{ created: '2018-03-01 10:37:59',
login: [ '2018-03-01 10:37:59' ],
userId: [ '1' ] },
{ created: '2018-03-01 10:39:52',
login: [ '2018-03-01 10:39:52' ],
userId: [ '1' ] },
{ created: '2018-03-01 10:41:49',
login: [ '2018-03-01 10:41:49' ],
userId: [ '1' ] },
{ created: '2018-03-01 15:34:52',
login: [ '2018-03-01 15:34:52' ],
userId: [ '1' ] } ]输出应该按创建日期(相同日期)分组，或者是否有其他方法可以这样做？请给出建议
回答 2
Stack Overflow用户
发布于 2018-06-06 22:11:19
SELECT name, userId, DATE(login) loginDate
FROM userRegister
WHERE logout IS NULL
GROUP BY name, userId, DATE(login)
HAVING COUNT(*)>1;Stack Overflow用户
发布于 2018-06-06 20:02:20
你可以这样做: EDIT: My Bad我已经忘记了group by
select userId, name, created, date(login) as date_login, count(*) as nb_login
from YourTable
WHERE logout IS NULL
GROUP BY userId, date_login
HAVING nb_login > 1注销时使用update进行编辑
SET @last_login:=NULL;
UPDATE userRegister r JOIN (
SELECT name, userId, login, if(logout is NULL, @last_login, logout) as logout
, @last_login :=login as dummy
FROM userRegister
ORDER BY userId, login desc) d USING(userId, login) SET r.logout = d.logout;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/50719033
复制相关文章
相似问题