急求SQL

小飛不想飛 2016-01-15 01:27:35
...全文
137 12 打赏 收藏 转发到动态 举报
写回复
用AI写文章
12 条回复
切换为时间正序
请发表友善的回复…
发表回复
Ginnnnnnnn 2016-01-15
  • 打赏
  • 举报
 回复 
SELECT d.Name AS [用户组],
		b.RealName AS [用户名称],
		ROW_NUMBER() OVER (PARTITION BY b.Ucf_UserID,CONVERT(VARCHAR(10),LogonTime,120) ORDER BY LogonTime) AS [日登陆顺序],
		a.LogonTime AS [登录时间],
		a.LogoutTime AS [退出时间],
		CONVERT(TIME,a.LogoutTime - a.LogonTime) AS [持续时间],
		CONVERT(TIME,DATEADD(ss,SUM(DATEDIFF(ss,a.LogonTime,a.LogoutTime)) OVER (PARTITION BY b.Ucf_UserID,CONVERT(VARCHAR(10),LogonTime,120) ),0)) [日使用总时间]
	FROM Sys_LogonLog a
		INNER JOIN Ucf_User b ON a.UserID = b.Ucf_UserID
		INNER JOIN Sys_UserRole c ON b.Ucf_UserID = c.UserID
		INNER JOIN Sys_Role d ON c.RoleID = d.RoleID
中国风 2016-01-15
  • 打赏
  • 举报
 回复
直接COPY上一贴表语句来改，刚发现楼主的表变了两个 Ucf_User --改改 
WITH Ta
AS
(
SELECT 
    a.Name as 用户组
    ,c.Name as 用户名
    ,d.IP
    ,ROW_NUMBER()OVER(PARTITION BY c.UserID,CONVERT(VARCHAR(10),d.LoginTime,120) ORDER BY d.LoginTime) AS LoginRank
    ,CONVERT(VARCHAR(10),d.LoginTime,120) AS DDay
    ,d.LoginTime
    ,d.LogoutTime
    ,DATEDIFF(ss,d.LoginTime,d.LogoutTime) AS TotalTime
    ,e.DDTotalTime
FROM Sys_Role as a
    inner join Sys_UserRole as b on a.RoleID=b.RoleID
    inner join Ucf_User as c on c.UserID=b.UserID
    LEFT join Sys_LoginLog as d on d.Usf_UserID=c.UserID
    CROSS APPLY(SELECT SUM(DATEDIFF(ss,d.LoginTime,d.LogoutTime))  FROM Pub_LoginTime WHERE UserID=d.UserID AND DATEDIFF(dd,LoginTime,d.LoginTime)=0)e(DDTotalTime)
    )
SELECT  用户组
       ,用户名
       ,'第'+RTRIM(LoginRank)+'次' AS LoginRank2
       ,IP
       ,LoginTime
       ,LogoutTime
       ,RTRIM(TotalTime/3600)+':'+RTRIM(TotalTime%3600/60)+':'+RTRIM(TotalTime%60) AS TotalTime
       ,RTRIM(DDTotalTime/3600)+':'+RTRIM(DDTotalTime%3600/60)+':'+RTRIM(DDTotalTime%60) AS DDTotalTime
FROM    Ta
ORDER BY 用户名,DDay,LoginRank DESC
中国风 2016-01-15
  • 打赏
  • 举报
 回复
显示格式少了":" 
WITH Ta
AS
(
SELECT 
    a.Name as 用户组
    ,c.Name as 用户名
    ,d.IP
    ,ROW_NUMBER()OVER(PARTITION BY c.UserID,CONVERT(VARCHAR(10),d.LoginTime,120) ORDER BY d.LoginTime) AS LoginRank
    ,CONVERT(VARCHAR(10),d.LoginTime,120) AS DDay
    ,d.LoginTime
    ,d.LogoutTime
    ,DATEDIFF(ss,d.LoginTime,d.LogoutTime) AS TotalTime
    ,e.DDTotalTime
FROM Sys_Role as a
    inner join Sys_UserRole as b on a.RoleID=b.RoleID
    inner join Sys_User as c on c.UserID=b.UserID
    LEFT join Sys_LoginLog as d on d.Usf_UserID=c.UserID
    CROSS APPLY(SELECT SUM(DATEDIFF(ss,d.LoginTime,d.LogoutTime))  FROM Pub_LoginTime WHERE UserID=d.UserID AND DATEDIFF(dd,LoginTime,d.LoginTime)=0)e(DDTotalTime)
    )
SELECT  用户组
       ,用户名
       ,'第'+RTRIM(LoginRank)+'次' AS LoginRank2
       ,IP
       ,LoginTime
       ,LogoutTime
       ,RTRIM(TotalTime/3600)+':'+RTRIM(TotalTime%3600/60)+':'+RTRIM(TotalTime%60) AS TotalTime
       ,RTRIM(DDTotalTime/3600)+':'+RTRIM(DDTotalTime%3600/60)+':'+RTRIM(DDTotalTime%60) AS DDTotalTime
FROM    Ta
ORDER BY 用户名,DDay,LoginRank DESC
中国风 2016-01-15
  • 打赏
  • 举报
 回复 
WITH Ta
AS
(
SELECT 
	a.Name as 用户组
	,c.Name as 用户名
	,d.IP
	,ROW_NUMBER()OVER(PARTITION BY c.UserID,CONVERT(VARCHAR(10),d.LoginTime,120) ORDER BY d.LoginTime) AS LoginRank
	,CONVERT(VARCHAR(10),d.LoginTime,120) AS DDay
	,d.LoginTime
	,d.LogoutTime
	,DATEDIFF(ss,d.LoginTime,d.LogoutTime) AS TotalTime
	,e.DDTotalTime
FROM Sys_Role as a
    inner join Sys_UserRole as b on a.RoleID=b.RoleID
    inner join Sys_User as c on c.UserID=b.UserID
    LEFT join Sys_LoginLog as d on d.Usf_UserID=c.UserID
    CROSS APPLY(SELECT SUM(DATEDIFF(ss,d.LoginTime,d.LogoutTime))  FROM Pub_LoginTime WHERE UserID=d.UserID AND DATEDIFF(dd,LoginTime,d.LoginTime)=0)e(DDTotalTime)
    )
SELECT  用户组
       ,用户名
       ,'第'+RTRIM(LoginRank)+'次' AS LoginRank2
       ,IP
       ,LoginTime
       ,LogoutTime
       ,RTRIM(TotalTime/3600)+':'+RTRIM(TotalTime%3600/60)+RTRIM(TotalTime%60) AS TotalTime
       ,RTRIM(DDTotalTime/3600)+':'+RTRIM(DDTotalTime%3600/60)+RTRIM(DDTotalTime%60) AS DDTotalTime
FROM    Ta
ORDER BY 用户名,DDay,LoginRank DESC
xdashewan 2016-01-15
  • 打赏
  • 举报
 回复
末尾再加order by a.userid, a.LogonTime desc
小飛不想飛 2016-01-15
  • 打赏
  • 举报
 回复
引用 6 楼 xdashewan 的回复:
[quote=引用 5 楼 xp447196763 的回复:] 最后日登录次数好像有点问题，时间小的应该是最先登录
with里面的那句sql里面有个orderby，把desc去掉[/quote] 去掉是对了，可是我想倒着排，比如从第3次、第2次，第1次，去掉之后就是从第1次开始了
xdashewan 2016-01-15
  • 打赏
  • 举报
 回复
引用 5 楼 xp447196763 的回复:
最后日登录次数好像有点问题，时间小的应该是最先登录
with里面的那句sql里面有个orderby，把desc去掉
小飛不想飛 2016-01-15
  • 打赏
  • 举报
 回复
引用 4 楼 xdashewan 的回复:
更正，copy的时候错了


with cte as

(

	select

	UserID,

	IP, 

	LogonTime, 

	LogoutTime, 

	DATEDIFF(SECOND, LogonTime, LogoutTime) as timediff,

	sum(DATEDIFF(SECOND, LogonTime, LogoutTime)) over (partition by UserID, CONVERT(varchar(100), LogonTime, 111)) as total, 

	ROW_NUMBER() over (partition by UserID, CONVERT(varchar(100), LogonTime, 111) order by LogonTime desc) as rowid

	from Sys_LogonLog

)

select d.Name, 

b.RealName, 

a.IP, 

a.LogonTime, 

a.LogoutTime, 

cast(floor(a.timediff / 3600) as varchar) + ':' + cast(floor(a.timediff % 3600 / 60) as varchar) + ':' + cast(floor(a.timediff % 3600 % 60) as varchar) as timediff, 

cast(floor(a.total / 3600) as varchar) + ':' + cast(floor(a.total % 3600 / 60) as varchar) + ':' + cast(floor(a.total % 3600 % 60) as varchar) as total, 

'第' + cast(rowid as varchar) + '次' as rowid

from cte a

inner join Ucf_User b on a.UserID = b.Ucf_UserID

inner join Sys_UserRole c on a.UserID = c.UserID

inner join Sys_Role d on c.RoleID = d.RoleID


最后日登录次数好像有点问题，时间小的应该是最先登录
xdashewan 2016-01-15
  • 打赏
  • 举报
 回复
更正，copy的时候错了 

with cte as
(
	select
	UserID,
	IP, 
	LogonTime, 
	LogoutTime, 
	DATEDIFF(SECOND, LogonTime, LogoutTime) as timediff,
	sum(DATEDIFF(SECOND, LogonTime, LogoutTime)) over (partition by UserID, CONVERT(varchar(100), LogonTime, 111)) as total, 
	ROW_NUMBER() over (partition by UserID, CONVERT(varchar(100), LogonTime, 111) order by LogonTime desc) as rowid
	from Sys_LogonLog
)
select d.Name, 
b.RealName, 
a.IP, 
a.LogonTime, 
a.LogoutTime, 
cast(floor(a.timediff / 3600) as varchar) + ':' + cast(floor(a.timediff % 3600 / 60) as varchar) + ':' + cast(floor(a.timediff % 3600 % 60) as varchar) as timediff, 
cast(floor(a.total / 3600) as varchar) + ':' + cast(floor(a.total % 3600 / 60) as varchar) + ':' + cast(floor(a.total % 3600 % 60) as varchar) as total, 
'第' + cast(rowid as varchar) + '次' as rowid
from cte a
inner join Ucf_User b on a.UserID = b.Ucf_UserID
inner join Sys_UserRole c on a.UserID = c.UserID
inner join Sys_Role d on c.RoleID = d.RoleID
xdashewan 2016-01-15
  • 打赏
  • 举报
 回复 

with cte as
(
	select
	UserID,
	IP, 
	LogonTime, 
	LogoutTime, 
	DATEDIFF(SECOND, LogonTime, LogoutTime) as timediff,
	sum(DATEDIFF(SECOND, LogonTime, LogoutTime)) over (partition by UserID, CONVERT(varchar(100), LogonTime, 111)) as total, 
	ROW_NUMBER() over (partition by UserID, CONVERT(varchar(100), LogonTime, 111) order by LogonTime desc) as rowid
	from Sys_LogonLog
)
select d.Name, 
b.RealName, 
a.IP, 
a.LogonTime, 
a.LogoutTime, 
cast(floor(timediff / 3600) as varchar) + ':' + cast(floor(timediff % 3600 / 60) as varchar) + ':' + cast(floor(timediff % 3600 % 60) as varchar) as per, 
sum(DATEDIFF(MINUTE, a.LogonTime, a.LogoutTime)) over (partition by a.UserID, CONVERT(varchar(100), a.LogonTime, 111)) as total, 
'第' + cast(ROW_NUMBER() over (partition by a.UserID, CONVERT(varchar(100), a.LogonTime, 111) order by a.LogonTime desc) as varchar) + '次' as rowid
from cte a
inner join Ucf_User b on a.UserID = b.Ucf_UserID
inner join Sys_UserRole c on a.UserID = c.UserID
inner join Sys_Role d on c.RoleID = d.RoleID
小飛不想飛 2016-01-15
  • 打赏
  • 举报
 回复
引用 1 楼 Tiger_Zhao 的回复:
给文本啊！回答你问题之前还有看图打字？

select * from Sys_LogonLog  --登录记录表   UserID:用户ID，LogonTime:登录时间，IP：IP地址，LogoutTime：退出时间
select * from Ucf_User	--用户表  Ucf_UserID：用户ID，RealName：用户名称
select * from Sys_Role	--用户组表 RoleID:用户组ID，Name：用户组名称
select * from Sys_UserRole	--用户和用户组关联表 UserID：用户ID，RoleID：用户组ID
--关联的字段表后面注释都有，这个数据就没办法造了，你们写出来我测测吧
Tiger_Zhao 2016-01-15
  • 打赏
  • 举报
 回复
给文本啊！回答你问题之前还有看图打字？
急求SQL语句解决报表问题
 有表A：cz_t_czr.mz(民族）,cz_t_czr.xb（性别）,cz_t_czr.bs（标示），字典表B：d_mz.bm(编码),d_mz.mc(名称)和字典表C：d_xb.bm(编码),d_xb.mc(名称)要求实现报表格式：民族          汉                       回                 蒙古                   藏 
SQL自动计算工龄
SELECT DATEDIFF(year,'2008-12-29（写入入职时间）','2019-12-30（写日记算到哪年）')/365 AS DiffDate GETDATE() //取当前日期，可以将其填入记算到哪年，获得时时更新的工龄数据
navicat运行sql文件时错误
记录一个navicat工具运行sql文件时遇到的坑： 错误如下： [ERR] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '; 1.首先将sql文件的内容复制出来...
java sql存储过程_急求~~~在java中怎么实现sql中的存储过程啊~
createprocedurepro_login@namevarchar(20),@passwordvarchar(20),@iintoutputasselect@i=count(lid)fromloginwherelname=@nameandlpassword=@passworddeclare@iintexecpro_login'a',...create procedure pro_login@...
急求VB+数据库，SQL-server学生学生学籍管理系统
急求学生学籍管理系统源代码
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章