使用 mysql 和 nodejs 使用注销日期更新登录日期 [英] Update login date with logout date using mysql and nodejs
问题描述
我有一个用户注册表.首先，我需要检查同一天是否在同一天通过同一个 catId 进行了多次登录.我需要用下一个登录字段值更新注销字段.
I have a userregister table. First i need to check for same day if there is multiple login by same catId on same day.Than i need to update the logout field with next login field value.
现在我已经根据常见的登录日期对数据进行了分组.但我需要使用 belogns 将注销字段更新为相同的 catId 和同一天.我不知道该怎么做
for now i have grouped data based on common login date. but i need to update logout field with belogns to same catId and same day. I am not getting how to do it
请建议怎么做?
用户注册表:
id | name | created | login | logout | userId | catId
-------------------------------------------------------------------------------------
1 |test | 2018-02-19 11:34:00 | 2018-02-19 11:34:00 | NULL | 1 | 11
2 |test | 2018-02-21 14:01:42 | 2018-02-21 14:01:42 | NULL | 1 | 11
3 |test | 2018-02-21 15:24:05 | 2018-02-21 15:24:05 | NULL | 1 | 12
4 |test | 2018-02-22 16:46:15 | 2018-02-22 16:46:15 | NULL | 1 | 11
5 |test2 | 2018-02-27 09:51:19 | 2018-02-27 09:51:19 | NULL | 2 | 11
6 |test2 | 2018-02-28 11:59:24 | 2018-02-28 11:59:24 | NULL | 2 | 13
7 |test |2018-03-01 10:37:59 | 2018-03-01 10:37:59 | NULL | 1 | 12
8 |test |2018-03-01 10:39:52 | 2018-03-01 10:39:52 | NULL | 1 | 12
9 |test |2018-03-01 10:41:49 | 2018-03-01 10:41:49 | NULL | 1 | 13
10 |test |2018-03-01 15:34:52 | 2018-03-01 15:34:52 | NULL | 1 | 13
代码尝试:
db.query("select *, date(login) as login_date from userRegister where logout is NULL limit 10", function (err, user) {
if (!_.size(user)){
console.log([])
}
else{
var userArr =_.groupBy(user,'login_date')
console.log(userArr) //array grouped by date
}
})
输出:
{ '2018-02-19':
[ RowData {
created: '2018-02-19 11:34:00',
login: '2018-02-19 11:34:00' ,
userId: '1',
catId: '11',
login_date: '2018-02-19' } ],
'2018-02-21':
[ RowData {
created: '2018-02-21 14:01:42',
login: '2018-02-21 14:01:42',
userId: '1',
catId: '11',
login_date: '2018-02-21' },
RowData {
created: '2018-02-21 15:24:05',
login: '2018-02-21 15:24:05',
userId: '1',
catId: '12',
login_date: '2018-02-21' } ],
'2018-02-22':
[ RowData {
created: '2018-02-22 16:46:15',
login: '2018-02-22 16:46:15',
userId: '1',
catId: '11',
login_date: '2018-02-22' }],
'2018-02-27':
[RowData {
created: '2018-02-27 09:51:19',
login: '2018-02-27 09:51:19',
userId: '2',
catId: '11',
login_date: '2018-02-27' }],
'2018-02-28':
[RowData {
created: '2018-02-28 11:59:24',
login: '2018-02-28 11:59:24',
userId: '2',
catId: '13',
login_date: '2018-02-28' }],
'2018-03-01':
[RowData {
created: '2018-03-01 10:37:59',
login: '2018-03-01 10:37:59',
userId: '1',
catId: '12',
login_date: '2018-03-01' },
RowData {
created: '2018-03-01 10:39:52',
login: '2018-03-01 10:39:52',
userId: '1',
catId: '12',
login_date: '2018-03-01' },
RowData {
created: '2018-03-01 10:41:49',
login: '2018-03-01 10:41:49',
userId: '1',
catId: '13',
login_date: '2018-03-01' },
RowData {
created: '2018-03-01 15:34:52',
login: '2018-03-01 15:34:52',
userId: '1',
catId: '13',
login_date: '2018-03-01' }],
}
更新代码:
var byCatId = _.groupBy(user, 'catId');
var grouppedArray = _.map(byCatId, function(array) {
return _.groupBy(array, 'login_date')
});
for(var k=0;k<grouppedArray[i][j].length;k++){
if(grouppedArray[i][j][k+1]){
var updatedLogOut = moment(grouppedArray[i][j][k+1].login).subtract(1, 'minutes').format('YYYY-MM-DD HH:mm:ss')
db.query('update userRegister set logout= ? where userId = ? and catId = ?', [updatedLogOut, 1, grouppedArray[i][j][k].userId, grouppedArray[i][j][k].catId], function (err, updatedLogout) {
}
}
}
这个编码是否正确?我的意思是这是不好的做法还是存在任何其他好的方法或替代方案.请帮忙
Is this coding is correct ? i mean is it bad way of doing or any other good method or alternative for this exist. Please help
在这个我收到错误SyntaxError: missing ) after argument list 在'INTERVAL 1 Minute'附近.我在这里想念的是什么?我找到了一个替代方案，即时刻(模块)
In this i am getting error SyntaxError: missing ) after argument list near 'INTERVAL 1 Minute'. what is that i am missing here ?
I found an alternative for this i.e moment (module)
推荐答案
正如我昨天在您的帖子中所说的，您可以使用一些变量在完整的 mysql 中执行此操作:
As i said in your post yesterday you can do this in full mysql with some variables:
SET @last_login:=NULL;
SET @last_user:=NULL;
SET @last_cat:=NULL;
UPDATE userRegister r JOIN (
SELECT name, userId, catId, login,
if((logout is NULL) AND (@last_user=userId) AND (@last_cat=catId), @last_login, logout) as logout
, @last_login :=login as dummy_log
, @last_user:=userId as dummy_user
, @last_cat:=catId as dummy_cat
FROM userRegister
ORDER BY userId, catId, login desc) d USING(userId, catId, login) SET r.logout = d.logout;
这篇关于使用 mysql 和 nodejs 使用注销日期更新登录日期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
