MYSQL - 总和不符合预期 [英] MYSQL - sum doesnt sum as intended

问题描述

现在是早上 04.17，我不知道出了什么问题！请帮忙！

It is 04.17 in the morning and i cant figure what is wrong! Help please!

我在表 user_logs 中有包含此类列的表:

I have table with such columns in table user_logs:

id | user_id | action | reference | time
----------------------------------------
1  |   1     | login  |     0     | 1333800404
2  |   1     | logout |     1     | 1333800424
3  |   1     | login  |     0     | 1333800434
4  |   1     | logout |     3     | 1333800444

并查询:

SELECT reference r,
  sum(time-(SELECT time FROM users_logs WHERE id = r)) time_of_logon
FROM users_logs
WHERE user_id = 1 AND action = 'logout'

不幸的是 sum() 函数返回了意外的值.

Unfortunately sum() function returns unexpected value.

如果我删除 sum() 我会得到这样的查询:

If I remove sum() I get such a query:

SELECT reference r,
  (time-(SELECT time FROM users_logs WHERE id = r)) time_of_logon
FROM users_logs
WHERE user_id = 1 AND action = 'logout'

和结果:

r | time_of_logon
-----------
1 | 20
3 | 10

正如预期的那样 - 我使用 user_id = 1 的操作注销"打印了所有行，从我从子查询中获得的注销时间(与注销相关的登录时间)递减.现在我有时间用户已登录.到目前为止一切顺利.现在，当我添加 sum (如在第一个查询中)时，我希望 time_of_logon 的总和(应该是 30).如果我使用 avg 函数而不是 sum 我会期望 15 ((10+20)/2).

As expected - i printed all rows with action 'logout' of user_id = 1 decrementing time of logoff from time i got from subquery (time of login connected with logout). Now i have time user was logged in. So far so good. Now, when I add sum (like in first query) I would expect sum of time_of_logon (should be 30). If I put avg function instead of sum i would expect 15 ((10+20)/2).

求和版本:我得到 60，平均版本 - 我得到 30.

Sum version: I get 60, avg version - i get 30.

'time' 字段的类型是整数.

Type of 'time' field is integer.

我的猜测:我猜想 mysql 以某种方式返回的不是 2 行，而是它显示的 4 行，或者在主查询下加倍了一些计算.因为 - avg 和 sum 结果都是应有的两倍.

My guessing: I guess that mysql somehow returns not 2 rows as it shows but it's working on 4 rows, or doubles some calculations beneath the main query. Because both - avg and sum result is twice as big as it should be.

也许是我的大脑不再工作的时间问题，但我无法弄清楚出了什么问题.请帮忙.

Maybe it's the time issue that my brain is not working anymore but i cant figure out what is wrong. Please help.

推荐答案

SELECT user_id, sum(time_of_logon) as sum_login_time
FROM
(
  SELECT user_id,
         reference as r, 
         (time-(SELECT time FROM users_logs WHERE id = r)) as time_of_logon
  FROM users_logs 
  WHERE user_id = 1 AND action = 'logout'
) subtable
GROUP BY user_id

你能做一个两步查询吗?

can you do a two step query?

这篇关于MYSQL - 总和不符合预期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！