我试图从我的user_log表中获得一天的第一人称(标记为user_log.userlog_log_type = 1),最后一天的最后一人(log_type = 2)。在用户中,我有user_id pk和user_name,而在user_log中,我的user_id在第一张表中有userlog_user_id FK。
现在我设法获得了每天的第一个条目和每天的最后一个条目。但是,当我使用内部联接来获取我想要的表(day,firstIn,lastOut)时,出现错误,并且似乎找不到我做错的事情:
如果有人可以提供帮助,我将非常感激。非常感谢!
最佳答案
看来您要花很多时间才能获得更多...我将从一个查询开始,每天获取最小和最大人数,然后从中开始。我想要用户日志时间戳记上的索引...
select
LogInOut.LogActivity,
LogInOut.JustDay,
LogInOut.What_Time,
ULMain.UserLog_User_ID,
U1.user_name
from
( select
day( UL.UserLog_Timestamp ) as JustDay,
max( UL.UserLog_Log_type ) as LogActivity,
min( UL.userlog_timestamp ) as What_Time
from
UserLog UL
where
UL.UserLog_Log_Type = 1
group by
day( UL.UserLog_Timestamp )
UNION
select
day( UL.UserLog_Timestamp ) as JustDay,
max( UL.UserLog_Log_type ) as LogActivity,
max( UL.userlog_timestamp ) as What_Time
from
UserLog UL
where
UL.UserLog_Log_Type = 2
group by
day( UL.UserLog_Timestamp ) ) LogInOut
JOIN UserLog ULMain
on LogInOut.What_Time = ULMain.UserLog_Timestamp
AND LogInOut.LogActivity = ULMain.UsrLog_Log_Type
JOIN Users U1
on ULMain.UserLog_User_ID = U1.User_ID
order by
LogInOut.JustDay,
LogInOut.LogActivity
将创建类似
LogActivity JustDay What_Time UserLog_User_ID user_name
1 Mar 1 7:56am 123 Bill Board
2 Mar 1 6:23pm 431 Eilean Dover
1 Mar 2 7:02am 98 Crystal Clear
2 Mar 2 6:47pm 221 Ben Dover
现在,如果您希望将其汇总到单行(交叉表),以便一行显示日期,那么谁在第一位,谁在最后一位,我将包装顶部的内容并添加类似以下内容的组
select
LogInOut.JustDay,
MIN( LogInOut.What_Time ) as EarlyLogin,
MAX( IF( LogInOut.LogActivity = 1, U1.User_Name, ' ' )) as EarlyUser,
MAX( LogInOut.What_Time ) as LastLogOut
MAX( IF( LogInOut.LogActivity = 2, U1.User_Name, ' ' )) as LastUser
from
(exact rest of from / join / order by clause)
group by
LogInOut.JustDay
JustDay EarlyLogin EarlyUser LastLogOut LastUser
Mar 1 7:56am Bill Board 6:23pm Eilean Dover
Mar 2 7:02am Crystal Clear 6:47pm Ben Dover