sql - 连接的最大计数

Question

我有 3 个表:

用户:

Id   Login
1    John
2    Bill
3    Jim

计算机:

Id   Name
1    Computer1
2    Computer2
3    Computer3
4    Computer4
5    Computer5

session :

UserId   ComputerId   Minutes
1        2            47
2        1            32
1        4            15
2        5            5
1        2            7
1        1            40
2        5            31

我想显示这个结果表:

Login   Total_sess   Total_min   Most_freq_computer   Sess_on_most_freq   Min_on_most_freq
John    4            109         Computer2            2                   54
Bill    3            68          Computer5            2                   36
Jim     -            -           -                    -                   -

我自己只能覆盖前 3 列:

SELECT Login, COUNT(sessions.UserId), SUM(Minutes) FROM users
LEFT JOIN sessions
ON users.Id = sessions.UserId GROUP BY users.Id

还有一些其他列:

SELECT main.*
FROM (SELECT UserId, ComputerId, COUNT(*) AS cnt ,SUM(Minutes)
FROM sessions
GROUP BY UserId, ComputerId) AS main
INNER JOIN (
SELECT ComputerId, MAX(cnt) AS maxCnt FROM (
SELECT ComputerId, UserId, COUNT(*) AS cnt FROM sessions GROUP BY ComputerId, UserId
)
AS Counts GROUP BY ComputerId) 
                    AS maxes
ON main.ComputerId = maxes.ComputerId
AND main.cnt = maxes.maxCnt

但我需要在一个查询中获取整个结果表。我觉得我做的事情完全错了。需要帮助。

Answer 1

给你:

SELECT    u.login, t1.total_sess, t1.total_min, t2.mf, t2.sess_mf, t2.min_mf
FROM      users u
LEFT JOIN (
  SELECT   userid, COUNT(minutes) AS total_sess, SUM(minutes) AS total_min
  FROM     sessions
  GROUP BY userid
) AS t1 ON t1.userid = u.id
LEFT JOIN (
  SELECT   userid, name AS mf, COUNT(*) AS sess_mf, SUM(minutes) AS min_mf
  FROM     sessions s
  JOIN     computers c ON c.id = s.computerid
  GROUP BY userid, computerid
  HAVING   COUNT(computerid) >= ALL(SELECT   COUNT(*)
                                    FROM     sessions s2
                                    WHERE    s2.userid = s.userid
                                    GROUP BY s2.computerid)
) AS t2 ON t2.userid = u.id

我使用的是 MySQL 语法，但它应该非常便携。

如果您还需要什么，请随时提出!

编辑:我更新了查询，之前的查询是错误的:(