mysql根据最近时间匹配,Mysql加入时间匹配

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

tabes;

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

values:

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

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

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

Thanks

SR

解决方案

Try this:

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

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.