MySQL Subquery calculations(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 子查询计算的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!
本文标题为:MySQL 子查询计算
基础教程推荐
- Sql Server 字符串到日期的转换 2021-01-01
- 使用pyodbc“不安全"的Python多处理和数据库访问? 2022-01-01
- 将数据从 MS SQL 迁移到 PostgreSQL? 2022-01-01
- ERROR 2006 (HY000): MySQL 服务器已经消失 2021-01-01
- SQL Server:只有 GROUP BY 中的最后一个条目 2021-01-01
- 在 VB.NET 中更新 SQL Server DateTime 列 2021-01-01
- SQL Server 2016更改对象所有者 2022-01-01
- 如何在 SQL Server 的嵌套过程中处理事务? 2021-01-01
- 无法在 ubuntu 中启动 mysql 服务器 2021-01-01
- SQL Server 中单行 MERGE/upsert 的语法 2021-01-01