mysql - mysql中按相同日期分组

Question

我有一个包含以下字段的 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' ] } ]

输出应该按创建日期(同一日期)分组，还是有其他方法可以做到这一点？请推荐

Answer 1

SELECT name, userId, DATE(login) loginDate
FROM userRegister 
WHERE logout IS NULL
GROUP BY name, userId, DATE(login)
HAVING COUNT(*)>1;

查看Demo on SQL Fiddle .