原始数据
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