时间戳

首页 > 解决方案 > SQL查询 - 选择一个月中每一天的第一条和最后一条记录

mysql - SQL查询 - 选择一个月中每一天的第一条和最后一条记录

问题描述

Mysql 5.7.30：我有两个表：

项目：projectID，timeNeeded

注册：registrationID、projectID、startDateTime

我想要实现的目标：对于当前月份（基于 startDateTime），我想通过选择当月每一天的第一条和最后一条记录来了​​解“timeNeeded”。如果一天只有 1 条记录，它仍然应该计算两次。

例如，如果一天有 4 个注册，我只想包括这 4 个中的第一个和最后一个。

我有点不确定如何从这里开始，我想有多种方法可以实现这一目标。速度不重要，只要比我的第一个想法好；使用多个查询和 PHP 来处理它。

样本数据和想要的结果：

Project table:      
project1    50  
project2    20  
project3    30  
        
Registation table:      (hour:minute hidden)
reg1    project1    2020-07-01
reg2    project1    2020-07-01
reg3    project3    2020-07-02
reg4    project3    2020-07-02
reg5    project2    2020-07-02
reg6    project2    2020-07-02
reg7    project3    2020-07-03
reg8    project1    2020-07-04
reg9    project3    2020-07-05
reg10   project2    2020-07-05
        
        
Result (projects.timeNeeded for first and last of each day):        
reg1    50  
reg2    50  
reg3    30  
reg6    20  
reg7    30  
reg7    30  
reg8    50  
reg8    50  
reg9    30  
reg10   20

标签： mysqlsql

解决方案

这个要求的棘手部分是只有 1 个注册的日期的双行，这就是我使用 UNION ALL 的原因。
需要聚合来获取每天的第一startDateTime天和最后一天并最终加入：

select r.registrationID, p.timeNeeded
from (
  select registrationID, projectID, startDateTime 
  from Registration
  union all
  select max(registrationID), max(projectID), max(startDateTime) 
  from Registration
  group by date(startDateTime)
  having count(*) = 1
) r
inner join ( 
  select date(startDateTime) date, 
         min(startDateTime) min_date,
         max(startDateTime) max_date
  from Registration
  where date_format(startDateTime, "%Y-%m") = date_format(current_date, "%Y-%m")
  group by date
) t on r.startDateTime in (t.min_date, t.max_date) 
inner join Project p on p.projectID = r.projectID
order by r.startDateTime

请参阅演示。
结果：

| registrationID | timeNeeded |
| -------------- | ---------- |
| reg1           | 50         |
| reg2           | 50         |
| reg3           | 30         |
| reg6           | 20         |
| reg7           | 30         |
| reg7           | 30         |
| reg8           | 50         |
| reg8           | 50         |
| reg9           | 30         |
| reg10          | 20         |

推荐阅读