现在的数据记录是id timePID ActionTime
1 1 2011-09-23 08:00:32
2 1 2011-09-23 08:00:40
3 2 2011-09-23 12:00:33
4 2 2011-09-23 12:02:37
5 1 2011-09-24 08:00:00
6 2 2011-09-24 12:00:37
要取的结果是id timePID ActionTime
1 1 2011-09-23 08:00:32
3 2 2011-09-23 12:00:33
5 1 2011-09-24 08:00:00
6 2 2011-09-24 12:00:37
就是说去掉重复的日期记录,但是根据timePID作为重复条件的
1 1 2011-09-23 08:00:32
2 1 2011-09-23 08:00:40
3 2 2011-09-23 12:00:33
4 2 2011-09-23 12:02:37
5 1 2011-09-24 08:00:00
6 2 2011-09-24 12:00:37
要取的结果是id timePID ActionTime
1 1 2011-09-23 08:00:32
3 2 2011-09-23 12:00:33
5 1 2011-09-24 08:00:00
6 2 2011-09-24 12:00:37
就是说去掉重复的日期记录,但是根据timePID作为重复条件的
SET @a=1;
SET @B=1;
CREATE TABLE ttha AS
SELECT *,@B:=IF(@a=a.`timePID`,@B,@B+1) AS jl,@a:=a.`timePID` FROM tth a;
SELECT * FROM TTHA A WHERE NOT EXISTS(SELECT 1 FROM TTHA WHERE A.JL=JL AND A.ID>ID)
同一天内可能有几个时间段的记录,比如8:00, 12:00, 17:00等,其实timePID就是一个外键8点对应1,12点对应2,17点对应3等,所以ActionTime的最后时间是不统一,但同一日期要取的就是某一天不同的时间段记录,可能有8点,也可能有12点和17点, 如要今天的记录是
1 2011-12-6 8:00:00
2 2011-09-24 12:10:37
3 2011-09-24 17:05:00
AND a.`ActionTime`>`ActionTime`
)
from tth a
WHERE NOT EXISTS(
SELECT 1 FROM tth
WHERE a.`timePID`=`timePID`
AND DATE(a.`ActionTime`)=DATE(`ActionTime`)
AND a.`ActionTime`>`ActionTime`
)
你自己的这个语句不是已经符合你的要求了吗?
from tth a
WHERE NOT EXISTS(
SELECT 1 FROM tth
WHERE a.`timePID`=`timePID`
AND DATE(a.`ActionTime`)=DATE(`ActionTime`)
AND a.id>id
)