MySQL 子查询计算

问题描述

我有这个 mysql 查询，当我搜索一个用户并且他有一个登录名(开始 - 结束)时，它会在下一行显示总数，但是如果用户有多个登录名(开始 - 结束)，它不会在下一行显示总数....

I have this mysql query and when I search for a user and he has one login(start - end) it shows the total on the next row alright but if the user has more than one login(start - end) it doesnt show the total on the next row....

SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name
   , start.timestamp start
   , end.timestamp end
   , timediff(end.timestamp, start.timestamp) duration 
FROM user u
   , user_group ug
   , (
      select *
         , (
            select event_id
            from event L2 
            where L2.timestamp > L1.timestamp
               and L2.user_bannerid = L1.user_bannerid
            order by timestamp limit 1  
           ) stop_id
      From event L1
     ) start
join event end on end.event_id = start.stop_id
where start.status = 'In'
   and end.status='Out'
   and u.user_bannerid = start.user_bannerid
   and ug.user_bannerid = u.user_bannerid
   and ug.group_id = start.group_id 

UNION

SELECT null, null, null, CAST(sum(duration) as Time)
FROM
(
   SELECT CONCAT(u.lastname, ', ', u.firstname) AS Name
      , start.timestamp start
      , end.timestamp end
      , timediff(end.timestamp, start.timestamp) duration 
   from user u
      , user_group ug
      , (
         select *
         , (
            select event_id
            from event L2
            where L2.timestamp > L1.timestamp
               and L2.user_bannerid = L1.user_bannerid
            order by timestamp
            limit 1
           ) stop_id
         from event L1
        ) start
   join event end on end.event_id = start.stop_id
   where start.status = 'In'
      and end.status = 'Out'
   and u.user_bannerid = start.user_bannerid
   and ug.user_bannerid = u.user_bannerid
      and ug.group_id = start.group_id 
) total

当用户只有一次登录时显示总正常

it show the total alright when the user has only one login

+----------------------------------------------------+---------------+
| Name   | start               | end                 | duration      |    
+----------------------------------------------------+---------------+
| User   | 2011-11-24 02:12:05 | 2011-11-24 02:12:20 | 00:00:15      |       
|        |                     |                     | 00:00:15      |
+----------------------------------------------------+---------------+ 

但是当用户多次登录时，它不显示总小时数，

But when the user has more than one login, it doesnt show the total hours,

    +----------------------------------------------------+---------------+
    | Name   | start               | end                 | duration      |    
    +----------------------------------------------------+---------------+
    | User   | 2011-11-24 02:12:05 | 2011-11-24 02:12:20 | 00:00:15      |       
    | User   | 2011-11-28 21:46:54 | 2011-11-28 21:53:01 | 00:06:17      |
    |        |                     |                     |               |
    +----------------------------------------------------+---------------+

我猜它与限制有关，但是如果我将限制更改为 1 以上，我会收到错误没有 1242 子查询返回多于一行."有人可以帮我改写查询以显示总小时数，无论他们有多少登录?

im guessing it has to do with the Limits, but if i change the limit to more than 1 i get "the error no 1242 Subquery returns more than one row." can someone please help me rephrase the query to show total hours no matter how many logins they have?

仍然有问题，所以我想出了一个新的查询，但后来我一直得到空而不是总数.知道为什么会这样吗?

still having problems with this so i came up with a new query but then i keep getting null instead of the total. any idea why this is so?

+----------------------------------------------------+---------------+
| Name   | start               | end                 | duration      |    
+----------------------------------------------------+---------------+
| User   | 2011-11-24 02:12:05 | 2011-11-24 02:12:20 | 00:00:15      |       
| User   | 2011-11-28 21:46:54 | 2011-11-28 21:53:01 | 00:06:17      |
| User   | 2011-11-28 21:46:54 | 2011-11-28 21:53:01 | null          |
+----------------------------------------------------+---------------+

SELECT
    CONCAT(u.lastname, ', ', u.firstname) AS Name,
    start.timestamp AS start,
    end.timestamp AS end,
    TIME(SUM(TIMEDIFF(end.timestamp, start.timestamp))) AS duration 
FROM user AS u
    INNER JOIN user_group AS ug ON u.user_bannerid = ug.user_bannerid
    INNER JOIN event AS start ON start.user_bannerid = u.user_bannerid AND start.status='In' AND start.group_id = ug.group_id
    INNER JOIN event AS end ON end.user_bannerid = u.user_bannerid AND end.status='Out' AND start.event_id < end.event_id 
GROUP BY start.event_id WITH ROLLUP

推荐答案

我认为您最好的选择是在事件表中添加一个 stop_id，然后当用户注销时，使用来自事件表的记录 ID 更新 In 事件出事件.

I think your best option would be to add a stop_id to the event table, then when the user logs out, update the In event with the record id from the Out event.

对于现有记录，您可以编写查询以根据问题中的逻辑更新记录.

For existing records, you could write a query to update the records based on your logic in the question.

一旦你这样做了，因为你在事件表中有一个 stop_id，所以获取开始和停止时间是一个相对简单的查询.

Once you do this, since you have a stop_id in the event table, getting the start and stop time is a relatively trivial query.

暂时不考虑用户信息，这就是您获取详细信息所需的全部内容:

Leaving the user information out for now, this is all you need to get the details:

SELECT start.timestamp start ,
       end.timestamp end ,
       timediff(end.timestamp, start.timestamp) duration 
  FROM event start join event end on end.event_id = start.stop_id

您不需要测试开始或停止状态，因为大概只有状态为 In 的事件才会有 stop_id 和内连接(假设是join 在 mysql 中的作用)防止 Out 记录被包含在顶层.

You don't need to test the start or stop status because presumably only those events with a status of In will have a stop_id and the inner join (assuming that's what join does in mysql) prevents Out records from being included at the top level.

这篇关于MySQL 子查询计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！