按8小时时段统计工单数据求助：含跨天时段及导出异常

1. 解决#1046 - No database selected错误 #

• 执行查询前先指定目标数据库：USE your_database_name;（替换为你的数据库实际名称）
• 或在表名前添加数据库前缀，例如写your_database_name.tickets而非tickets

2. 实现跨天8小时时段分组统计 #

假设工单时间字段为create_time（datetime/timestamp类型），以下语句可将所有时间映射到三个8小时组，并计算所需统计值：

USE your_database_name;
SELECT
  CASE
    WHEN HOUR(create_time) BETWEEN 7 AND 14 THEN '07:00-15:00'
    WHEN HOUR(create_time) BETWEEN 15 AND 22 THEN '15:00-23:00'
    ELSE '23:00-次日07:00' -- 覆盖23:00-23:59和00:00-06:59的跨天时段
  END AS time_slot,
  COUNT(ticket_Id) AS total_ticket_Id,
  -- 计算up_ticket_Id：总数≤4则取总数，否则取4
  IF(COUNT(ticket_Id) <= 4, COUNT(ticket_Id), 4) AS up_ticket_Id,
  -- 计算down_ticket_Id：总数>4则取超出部分，否则为0
  IF(COUNT(ticket_Id) > 4, COUNT(ticket_Id) - 4, 0) AS down_ticket_Id
FROM tickets
-- 若需按每日统计三个时段，添加以下分组条件
-- GROUP BY DATE(create_time), time_slot
GROUP BY time_slot;

3. 确保三个时段分组完整、导出有数据 #

如果部分时段无工单导致分组缺失，可先预定义三个时段，再左联工单表，保证所有时段都显示（无数据时统计值为0）：

USE your_database_name;
WITH predefined_slots AS (
  SELECT '07:00-15:00' AS slot UNION ALL
  SELECT '15:00-23:00' AS slot UNION ALL
  SELECT '23:00-次日07:00' AS slot
)
SELECT
  ps.slot AS time_slot,
  COALESCE(COUNT(t.ticket_Id), 0) AS total_ticket_Id,
  IF(COALESCE(COUNT(t.ticket_Id), 0) <= 4, COALESCE(COUNT(t.ticket_Id), 0), 4) AS up_ticket_Id,
  IF(COALESCE(COUNT(t.ticket_Id), 0) > 4, COALESCE(COUNT(t.ticket_Id), 0) - 4, 0) AS down_ticket_Id
FROM predefined_slots ps
LEFT JOIN tickets t ON (
  CASE
    WHEN ps.slot = '07:00-15:00' THEN HOUR(t.create_time) BETWEEN 7 AND 14
    WHEN ps.slot = '15:00-23:00' THEN HOUR(t.create_time) BETWEEN 15 AND 22
    ELSE HOUR(t.create_time) IN (23,0,1,2,3,4,5,6)
  END
)
-- 按每日统计时添加：GROUP BY DATE(t.create_time), ps.slot
GROUP BY ps.slot;

注意：WITH子句需要MySQL 8.0及以上版本支持，低版本可改用子查询方式实现。

4. 创建统计视图 #

确保语句合法且指定了数据库，创建视图示例：

CREATE VIEW ticket_slot_stats AS
SELECT
  CASE
    WHEN HOUR(create_time) BETWEEN 7 AND 14 THEN '07:00-15:00'
    WHEN HOUR(create_time) BETWEEN 15 AND 22 THEN '15:00-23:00'
    ELSE '23:00-次日07:00'
  END AS time_slot,
  COUNT(ticket_Id) AS total_ticket_Id,
  IF(COUNT(ticket_Id) <= 4, COUNT(ticket_Id), 4) AS up_ticket_Id,
  IF(COUNT(ticket_Id) > 4, COUNT(ticket_Id) - 4, 0) AS down_ticket_Id
FROM your_database_name.tickets
GROUP BY time_slot;

内容的提问来源于stack exchange，提问作者Marco