高人求助，SQL问题！

 

原始数据

 

		TERMINAL_ID	MAXDATE	TERMINAL_ID	OCCUR_DATE_TIME	TROUBLE_CD
	1	12345	20100401102754	12345	20100401102754	210
	2	12345	20100401102754	12345	20100401102754	211
	3	12345	20100401102754	12345	20100401102754	?09
	4	12346	20100401102770	12346	20100401102770	?43J
	5	12347	20100401102780	12347	20100401102780	?42a
	6	12348	20100401102790	12348	20100401102790	?43J
	7	12349	20100401102800	12349	20100401102800	209
	8	12350	20100401102820	12350	20100401102820	?117
	9	12351	20100401102830	12351	20100401102830	211
	10	12352	20100401102840	12352	20100401102840	?42a
	11	12353	20100401102850	12353	20100401102850	?43J

 

想要的数据

		TERMINAL_ID	MAXDATE	TERMINAL_ID	OCCUR_DATE_TIME	TROUBLE_CD
	1	12345	20100401102754	12345	20100401102754	210
	2	12346	20100401102770	12346	20100401102770	?43J
	3	12347	20100401102780	12347	20100401102780	?42a
	4	12348	20100401102790	12348	20100401102790	?43J
	5	12349	20100401102800	12349	20100401102800	209
	6	12350	20100401102820	12350	20100401102820	?117
	7	12351	20100401102830	12351	20100401102830	211
	8	12352	20100401102840	12352	20100401102840	?42a
	9	12353	20100401102850	12353	20100401102850	?43J

 

 

 

问题是：

现在的需求是 一个terminal_id 对应一个时间， 可当时间相等时，怎么人已筛选出一条信息。

就是，上面数据里面 terminal_id 位 12345的有3条数据，怎么筛选出一条

 

我的SQL

 

SELECT DISTINCT F2.OCCUR_DATE_TIME, F1.TERMINAL_ID,  F2.TROUBLE_CD

FROM

(

SELECT T1.TERMINAL_ID, MAX(T1.OCCUR_DATE_TIME) MaxDate  FROM MON.ATMTROTBL T1

WHERE

 1 = 1

 AND T1.terminal_id Like '12%'

 AND T1.occur_date_time <= '20101011010101'

 AND T1.trouble_cd <> '?00' AND T1.trouble_cd <> '?100'

 AND T1.trouble_cd <> '?30' AND T1.trouble_cd <> '?31'

 AND T1.trouble_cd <> '?35' AND T1.trouble_cd <> '@01'

 AND T1.trouble_cd <> '@02' AND T1.trouble_cd <> '@03'

 AND T1.trouble_cd <> '@04' AND T1.trouble_cd <> '@05'

 AND T1.trouble_cd <> '@08' AND T1.trouble_cd <> '201'

 AND T1.trouble_cd <> '202' AND T1.trouble_cd <> '203'

 AND T1.trouble_cd <> '204' AND T1.trouble_cd <> '205'

 AND T1.trouble_cd <> '207' AND T1.trouble_cd <> '208'

 AND T1.trouble_cd <> '217' AND T1.trouble_cd <> '218'

 AND T1.trouble_cd <> '219' AND T1.trouble_cd <> '220'

 AND T1.trouble_cd <> '221' AND T1.trouble_cd <> '222'

 AND T1.trouble_cd <> '223' AND T1.trouble_cd <> '224'

 AND T1.trouble_cd <> '=01' AND T1.trouble_cd <> '=05'

 AND T1.trouble_cd <> '=06' AND T1.trouble_cd <> '=07'

 AND T1.trouble_cd <> '=08' AND T1.trouble_cd <> '=12'

 AND T1.trouble_cd <> '=16' AND T1.trouble_cd <> '>01'

 AND T1.trouble_cd <> '>02' AND T1.trouble_cd <> '>03'

 AND T1.trouble_cd <> '>06' AND T1.trouble_cd <> '>07'

 AND T1.trouble_cd <> '>08' AND T1.trouble_cd <> '>09'

 AND T1.trouble_cd <> '>10' AND T1.trouble_cd <> '>11'

 AND T1.trouble_cd <> '>12' AND T1.trouble_cd <> '>16'

 AND T1.trouble_cd <> '?4A' AND T1.trouble_cd <> '?4B'

 AND T1.trouble_cd <> '?4C' AND T1.trouble_cd <> '?4D'

 AND T1.trouble_cd <> '?4E' AND T1.trouble_cd <> '?4F'

 AND T1.trouble_cd <> '?4G' AND T1.trouble_cd <> '?4H'

GROUP BY TERMINAL_ID

 

) F1

 Left JOIN

(

SELECT

 T1.terminal_id,

 T1.occur_date_time,

 T1.trouble_cd

 FROM

 MON.ATMTROTBL T1

ORDER BY

 T1.terminal_id ASC, T1.occur_date_time DESC

) F2

 ON

F1.TERMINAL_ID = F2.TERMINAL_ID

AND F1. MaxDate  = F2.OCCUR_DATE_TIME