MS SQL 希望结果来自单个表想要生成报告

Question

Table Schema
(
DeviceLogId int
DeviceId    int
UserId      int
LogDate    datetime
)

表数据

112 25  66  2015-07-22 11:02:15.000
332 25  66  2015-07-22 17:29:25.000
555 25  88  2015-07-23 19:09:35.000
779 25  67  2015-07-24 16:23:49.000
1003    29  17  2015-07-18 13:03:04.000

我想要这样的输出

Intime                  Outtime                logdate   Incount    OutCount
2015-01-01 10:22:29.000 2015-01-01 19:58:43.000 2015-01-01  7       11
2015-01-02 09:52:26.000 2015-01-02 20:25:25.000 2015-01-02  2       2

我为一个用户 ID 创建了查询，但如果我想为多个用户 ID 工作

select e.Intime,e.Outtime,e.logdate,e.Incount,e.OutCount from
    (
     select a.intime as Intime,b.outtime as Outtime,c.logdate,c.InCount as Incount,d.OutCount as OutCount from    

      ( 
          select min(logdate) intime,cast(LogDate as date) logdate,userid
          from DeviceLogs  where deviceid in (26,31) and  cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
          group by cast(LogDate as date), UserId
      )a  inner join 
      (
          select max(logdate) outtime,cast(LogDate as date) logdate,userid from DeviceLogs
          where deviceid in (25,30) and   cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
          group by cast(LogDate as date), UserId
      ) b on a.logdate = b.logdate
      left join 
      (
       select UserId, cast(LogDate as date) logdate ,count(DeviceLogId) as InCount from DeviceLogs 
        where deviceid in (26,31) and  cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
        Group by UserId, cast(LogDate as date) 
       )c on b.logdate = c.logdate    
       left join
       (
       select UserId, cast(LogDate as date) logdate ,count(DeviceLogId) as OutCount from DeviceLogs 
        where deviceid in (25,30) and  cast(LogDate as date) between '2015-01-01' and '2015-01-02' and UserId=7
        Group by UserId, cast(LogDate as date) 
        )d on c.logdate = d.logdate     
    )e  

所以我想获取所有用户数据。等待回复

我想检索所有用户数据并与网格绑定具有更好的性能

Answer 1

我真的不明白你对 min(( max(( 的推理。 也许您正在尝试在此期间显示第一次/最后一次登录？

希望对你有帮助：

select 
   min(intime)   min_intime, 
   min(outtime)  min_outtime, 
   min(logdate)  min_logdate, 
   max(intime)   max_intime, 
   max(outtime)  max_outtime, 
   max(logdate)  max_logdate,
   count(*)      [tot], 
   userid
from devicelogs
where logdate between '01-01-2000' and '01-01-2001'
group by userid

Answer 2

我希望这对你有帮助。

 SELECT inlog.UserId, inlog.date, intime,incount,outtime,ISNULL(outcount,0) 
FROM 
    (SELECT 
        ILog.UserId, CAST(ILog.LogDate as date) date , Min(ILog.LogDate) intime, Count(ILog.DeviceLogId) incount
      from 
        DeviceLogs ILog
      where
        ILog.DeviceId in (25) -- in deviceIds
      GROUP BY 
         ILog.UserId, CAST(ILog.LogDate as date)) inlog
LEFT OUTER join 
    (SELECT 
        OLog.UserId, CAST(OLog.LogDate as date) date , Max(OLog.LogDate) outtime, Count(OLog.DeviceLogId) outcount
        from 
        DeviceLogs OLog
        where 
        OLog.DeviceId in (26) -- out deviceIds
        GROUP BY 
        OLog.UserId, CAST(OLog.LogDate as date)) outlog
ON  inlog.UserId =  outlog.UserId