SQL，唯一筛选问题，牛人请帮忙

最新推荐文章于 2024-06-21 17:54:11 发布

冰川鱿鱼大王

最新推荐文章于 2024-06-21 17:54:11 发布

阅读量177

点赞数

文章标签： SQL

本文链接：https://blog.csdn.net/wen0301/article/details/83635085

版权

原始数据

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