Mysql加入时间匹配

Question

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

标签；

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`)

值(value)观:

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"

我正在寻找这样的输出:

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

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 

基本上我想列出该作业期间的所有作业和相应的 cpu 使用情况。

谢谢SR

Answer 1

试试这个:

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 的变化。