MySQL加入时间匹配

本文介绍了MySQL加入时间匹配的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有两个表cpuinfo和jobinfo.我想使用这两种数据创建报告.

I have two table cpuinfo and jobinfo. I want to create report using both data.

标签；

CREATE TABLE `cpuinfo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `usagetime` datetime DEFAULT NULL,
  `cpuusage` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)

CREATE TABLE `jobinfo` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `starttime` datetime NOT NULL,
  `endtime` datetime DEFAULT NULL,
  `jobname` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)

值:

cpuinfo
id,usagetime,cpuusage
1,"2011-03-12 11:10:01",40
2,"2011-03-12 11:10:31",45
3,"2011-03-12 11:11:01",45
4,"2011-03-12 11:11:31",43
5,"2011-03-12 11:12:01",55
6,"2011-03-12 11:12:31",49

jobinfo
id,starttime,endtime,jobname
1,"2011-03-12 11:10:01","2011-03-12 11:10:08","job a"
2,"2011-03-12 11:10:05","2011-03-12 11:10:18","job b"
3,"2011-03-12 11:10:15","2011-03-12 11:10:28","job c"
4,"2011-03-12 11:10:31","2011-03-12 11:10:38","job d"
5,"2011-03-12 11:10:45","2011-03-12 11:10:48","job e"
6,"2011-03-12 11:10:55","2011-03-12 11:10:55","job f"
7,"2011-03-12 11:11:31","2011-03-12 11:11:43","job d"
8,"2011-03-12 11:11:45","2011-03-12 11:11:49","job e"
9,"2011-03-12 11:11:55","2011-03-12 11:11:59","job f"
10,"2011-03-12 11:12:31","2011-03-12 11:12:43","job d"
11,"2011-03-12 11:12:45","2011-03-12 11:12:49","job e"
12,"2011-03-12 11:12:55","2011-03-12 11:12:59","job f"

我正在寻找这样的输出:

I am looking output like this:

starttime,endtime,jobname,cpuusage
"2011-03-12 11:10:01","2011-03-12 11:10:08","job a",40
"2011-03-12 11:10:05","2011-03-12 11:10:18","job b",40
"2011-03-12 11:10:15","2011-03-12 11:10:28","job c",40
"2011-03-12 11:10:31","2011-03-12 11:10:38","job d",45
"2011-03-12 11:10:45","2011-03-12 11:10:48","job e",45
"2011-03-12 11:10:55","2011-03-12 11:10:55","job f",45
"2011-03-12 11:11:31","2011-03-12 11:11:43","job d",43
"2011-03-12 11:11:45","2011-03-12 11:11:49","job e",43
"2011-03-12 11:11:55","2011-03-12 11:11:59","job f",43
"2011-03-12 11:12:31","2011-03-12 11:12:43","job d",49
"2011-03-12 11:12:45","2011-03-12 11:12:49","job e",49
"2011-03-12 11:12:55","2011-03-12 11:12:59","job f",49

此SQL将不匹配的sql值设置为null

This SQL gives non-matching sql values to null

select a.starttime, a.endtime, a.jobname,b.cpuusage  from jobinfo a
    left join cpuinfo b on b.usagetime >= a.starttime and  b.usagetime <= a.endtime

基本上，我想列出该工作时间内的所有工作和相应的cpuusage.

Basically I want to list all the jobs and corresponding cpuusage during that job time.

谢谢SR

推荐答案

尝试一下:

SELECT j.id, j.starttime, j.endtime, j.jobname, c.cpuusage
FROM
(
    SELECT j.id, j.starttime, j.endtime, j.jobname, MAX(c.usagetime) AS usagetime
    FROM jobinfo AS j
    LEFT JOIN cpuinfo AS c
    ON c.usagetime <= j.starttime
    GROUP BY j.id
) AS j
JOIN cpuinfo AS c
ON j.usagetime = c.usagetime

这将提供您想要的输出.它查找每个作业开始之前的最新cpuusage值.作业运行时，它不处理cpuusage中的更改.

This gives the output you wanted. It finds the most recent value of cpuusage before the starttime of each job. It doesn't handle changes in cpuusage while the job is running.

这篇关于MySQL加入时间匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

CpuUsage