主页 / coding / 问题 / 79487969
Accepted
AQuirky
Asked: 2025-03-06 08:23:38 +0800 CST

MySQL 中多个组的时间差异总和

  • 772

我在 MySQL 中有一个表...

# id, admin_id, appointment_id, timestamp
'1', '10', '1', '2025-03-01 08:00:00'
'2', '10', '1', '2025-03-01 09:00:00'
'3', '10', '2', '2025-04-01 08:00:00'
'4', '10', '2', '2025-04-01 09:00:00'
'5', '20', '1', '2025-05-01 08:00:00'
'6', '20', '1', '2025-05-01 09:00:00'
'7', '20', '2', '2025-06-01 08:00:00'
'8', '20', '2', '2025-06-01 09:00:00'

我想要做的是计算管理员预约的最小/最大时间差的总和。

我可以轻松地按管理员和约会进行分组

SELECT *,
  min(timestamp),
  max(timestamp),
  max(unix_timestamp(timestamp))-min(unix_timestamp(timestamp)) as photo_time,
  count(id) as photo_count
FROM scratch.photo_time
GROUP BY admin_id,appointment_id

其结果是

# id, admin_id, appointment_id, timestamp, min(timestamp), max(timestamp), photo_time, photo_count
'1', '10', '1', '2025-03-01 08:00:00', '2025-03-01 08:00:00', '2025-03-01 09:00:00', '3600', '2'
'3', '10', '2', '2025-04-01 08:00:00', '2025-04-01 08:00:00', '2025-04-01 09:00:00', '3600', '2'
'5', '20', '1', '2025-05-01 08:00:00', '2025-05-01 08:00:00', '2025-05-01 09:00:00', '3600', '2'
'7', '20', '2', '2025-06-01 08:00:00', '2025-06-01 08:00:00', '2025-06-01 09:00:00', '3600', '2'

我想要的是一个仅按管理员分组的表格，其中汇总了每个约会的时间差异。

我最接近的答案是这个......

SELECT *,
  min(timestamp),
  max(timestamp),
  sum((max(unix_timestamp(timestamp)) over (partition by appointment_id))-min(unix_timestamp(timestamp)) over (partition by appointment_id)) as photo_time,
  count(id) as photo_count
FROM scratch.photo_time
GROUP BY admin_id

但这会产生错误。

正确的表格只有2行，每行照片数为4，照片时间为7200。

此查询的正确 MySQL 语法是什么？

sql

1 个回答

  1. Best Answer

    您的第一个查询将需要计算中间值（逐日）photo_time。
    然后，您只需在上层选择SUM()第一个查询的结果（现在充当子选择）。

    SELECT admin_id, sum(photo_time)
FROM
(
  -- v v v This is your query, untouched:
  SELECT admin_id, appointment_id,
    min(timestamp),
    max(timestamp),
    max(unix_timestamp(timestamp))-min(unix_timestamp(timestamp)) as photo_time,
    count(id) as photo_count
  FROM photo_time
  GROUP BY admin_id,appointment_id
  -- ^ ^ ^ This was your query, untouched.
) by_appointment
GROUP BY admin_id;
    管理员 ID 总和（照片时间）
    10 7200
    20 7200

    （并在 Fiddle 中运行）

    • 1

相关问题