CREATE TABLE `foo` (
`pk` int(11) NOT NULL AUTO_INCREMENT,
`ID` varchar(45) DEFAULT NULL,
`Delivery_Date` date NOT NULL,
`Hour_Ending` time NOT NULL,
`Resource_Name` varchar(45) DEFAULT NULL, 
`Eff_HASL` float DEFAULT NULL,
`Submit_Time` datetime DEFAULT NULL,
PRIMARY KEY (`pk`)
);

INSERT INTO `foo` (`pk`,`id`,`Delivery_Date`,`Hour_Ending`,`Resource_Name`,`eff_hasl`,`Submit_Time`) VALUES 
(1,'2435672','2012-01-25','09:00:00','BASTEN_1',0,'2012-01-18 08:33:52'),
(2,'2435672','2012-01-25','09:00:00','BASTEN_1',0,'2012-01-19 07:47:33'),
(11,'2435672','2012-01-25','09:00:00','BASTEN_1',269,'2012-01-25 05:50:05'),

(3,'2435672','2012-01-26','09:00:00','BASTEN_2',111,'2012-01-18 08:33:52'),
(4,'2435672','2012-01-26','09:00:00','BASTEN_2',0,'2012-01-24 07:47:33'),
(5,'2435672','2012-01-26','09:00:00','BASTEN_2',269,'2012-01-25 05:45:05'),
(6,'2435672','2012-01-26','09:00:00','BASTEN_2',269,'2012-01-26 05:50:05'),

(7,'2435672','2012-01-27','09:00:00','BASTEN_3',111,'2012-01-18 08:33:52'),
(8,'2435672','2012-01-27','09:00:00','BASTEN_3',0,'2012-01-24 07:47:33'),
(9,'2435672','2012-01-27','09:00:00','BASTEN_3',269,'2012-01-27 05:45:05'),
(10,'2435672','2012-01-27','09:00:00','BASTEN_3',500,'2012-01-27 05:50:05');

SELECT * FROM foo;
+----+---------+---------------+-------------+---------------+----------+---------------------+
| pk | ID      | Delivery_Date | Hour_Ending | Resource_Name | Eff_HASL | Submit_Time         |
+----+---------+---------------+-------------+---------------+----------+---------------------+
|  1 | 2435672 | 2012-01-25    | 09:00:00    | BASTEN_1      |        0 | 2012-01-18 08:33:52 |
|  2 | 2435672 | 2012-01-25    | 09:00:00    | BASTEN_1      |        0 | 2012-01-19 07:47:33 |
|  3 | 2435672 | 2012-01-26    | 09:00:00    | BASTEN_2      |      111 | 2012-01-18 08:33:52 |
|  4 | 2435672 | 2012-01-26    | 09:00:00    | BASTEN_2      |        0 | 2012-01-24 07:47:33 |
|  5 | 2435672 | 2012-01-26    | 09:00:00    | BASTEN_2      |      269 | 2012-01-25 05:45:05 |
|  6 | 2435672 | 2012-01-26    | 09:00:00    | BASTEN_2      |      269 | 2012-01-26 05:50:05 |
|  7 | 2435672 | 2012-01-27    | 09:00:00    | BASTEN_3      |      111 | 2012-01-18 08:33:52 |
|  8 | 2435672 | 2012-01-27    | 09:00:00    | BASTEN_3      |        0 | 2012-01-24 07:47:33 |
|  9 | 2435672 | 2012-01-27    | 09:00:00    | BASTEN_3      |      269 | 2012-01-27 05:45:05 |
| 10 | 2435672 | 2012-01-27    | 09:00:00    | BASTEN_3      |      500 | 2012-01-27 05:50:05 |
| 11 | 2435672 | 2012-01-25    | 09:00:00    | BASTEN_1      |      269 | 2012-01-25 05:50:05 |
+----+---------+---------------+-------------+---------------+----------+---------------------+

我有一张表，上面有一系列特定时间的更新，并用交货日期和结束时间（我们称之为营业时间）来表示。提交时间是更新发生的时间。更新可以在运行时间前14天开始。
更新可以在14天前发生一次，并且永远不会更改，也可以在工作时间之前每小时更改一次。大多数情况下，eff\ U hasl会发生几次更改，但很少在工作时间的同一天进行这些更改。
我的查询试图通过使用delivery\u date=date（submit\u time）子句来捕获与工作时间同一天发生的更改。我不知道如何设置一个初始状态来进行比较。此查询只查看当天提交的内容，但如果有当天提交的内容，则无法与最近提交的内容进行比较。我只在乎效率是否改变。
我正在寻找一个三角洲为每个资源为每个交货日期为每小时结束为整个一年。如果资源没有同一天提交，则delta应该=0，如果有，则应该比较同一天提交的条目列表和该天之前最后一次提交的条目列表。所以结果应该是这样的：
basten_1 delta for 2012-01-25 09:00:00=269类似于：
‘2012-01-25’，‘09:00:00’，‘巴斯滕1’，269
basten_2 delta for 2012-01-26 09:00:00=0类似
“2012-01-26”，“09:00:00”，“巴斯滕2”，0
巴斯滕3=500。。。

select Delivery_Date, Hour_Ending, Resource_Name, min(eff_hasl)-max(eff_hasl) delta
from foo 
where Delivery_Date = date(Submit_Time)
group by Resource_Name, date(Delivery_Date), hour(hour_ending) having delta <> 0;

我想应该有一些子查询？我想包括最新的提交日期之前（营业时间）进行比较，在清单中。比如：

select a.Delivery_Date, a.Hour_Ending, a.Resource_Name, min(b.eff_hasl)-max(a.eff_hasl) delta
from foo as a
inner join (SELECT Resource_Name, Delivery_Date, Hour_Ending, submit_time, 
    eff_hasl  
    FROM foo
    where date(submit_time) < Delivery_date
    GROUP BY resource_name, delivery_date, Hour_Ending
    order by Submit_Time desc
    limit 1
) as b
ON      (b.resource_name = a.resource_name AND b.delivery_date = 
         a.delivery_date AND b.hour_ending = a.hour_ending)
where a.Delivery_Date = date(a.Submit_Time)
group by a.Resource_Name, date(a.Delivery_Date), hour(a.hour_ending) having delta <> 0;