mysql中按相同日期分组

erhoui1w 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(459)

我有一个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' ] } ]

输出应该按创建日期（同一日期）分组，或者有其他方法吗？请建议

mysql group-by node.js

来源：https://stackoverflow.com/questions/50719033/group-by-same-date-in-mysql

2条答案

按热度按时间

avwztpqn1#

你可以这样做：编辑：我的坏我已经忘记了该组由

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

使用“注销时更新”字段进行编辑

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;

赞(0）回复(0）举报 2021-06-20

63lcw9qa2#

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

查看SQLFiddle的演示。

赞(0）回复(0）举报 2021-06-20

我来回答

mysql中按相同日期分组

2条答案

相关问题

热门标签

最新问答