本文介绍了从条件中减去当前的记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
您好专家，
我需要您的专业指导来完成此查询。
i有一个名为'FO'的表有3列ID，阅读，Date_time
我不知道从哪里插入数据，但它很容易受到攻击:(
那里是许多具有不同ID的重复数据（可以作为主列）。
我的任务是首先消除重复的行（具有相同的Date_time和读取但具有不同的ID）然后从前一个记录中减去'Reading'，如果之前的'Reading'小于或等于current。如果当前'Reading'小于之前，则当前将在给定的日期范围内视为结果。
样本数据：
ID读取日期_时间
7 1465564 09-04-2018 03:31:52
8 1465564 09-04-2018 03:31:52
13 1465564 09-04-2018 10:53:09
14 1465564 09-04-2018 10:53:09
15 1465568 09-04-2018 13:35:11
16 1465568 09-04-2018 13:35:11
20 1465568 09-04-2018 18:37:01
21 1465595 09-04-2018 19:37:15
22 1465601 09-04-2018 19： 37:38
23 1465601 09-04-2018 19:37:38
24 1465608 09-04-2018 22:24:11
30 1465608 09-04-2018 22:24： 11
31 1465809 10-04-2018 05:32:25
35 2832 10-04-2018 09:07:15
43 1465833 10-04-2018 09:42:53
44 1466228 10-04-2018 18:37:15
45 1466301 10-04-2018 19:22:47
46 6322 10-04-2018 20:36:32
47 1466331 10-04-2018 21:22:51
48 1466361 10-04-2018 22:36:15
49 1466376 10-04-2018 23:37:13
53 1469714 11-04-2018 03:37:15
54 9365 11-04-2018 04:37:15
55 1471514 11-04-2018 05:37:15
56 1472185 11 -04-2018 06:21:59
61 1472192 11-04-2018 11:19:07
62 1472192 11-04-2018 11:19:07
63 2192 11-04 -2018 13:04:17
68 1472208 11-04-2018 18:23:41
69 1472212 11-04-2018 19:20:27
70 1472212 11-04-2018 19:20:27
71 1472212 11-04-2018 19:20:27
72 1472212 11-04-2018 19:20:27
73 1472212 11-04-2018 19： 20:27
74 1472443 12-04-2018 00:37:15
75 1473078 12-04-2018 01:37:15
76 1473978 12-04-2018 02:37： 15
77 1474299 12-04-2018 03:37:13
78 1474301 12-04-2018 03:42:45
79 5182 12-04-2018 05:37:15
147 1476082 12-04-2018 06:37:15
81 1476242 12-04-2018 06:47:55
96 1476242 12-04-2018 06:47:55
97 1476752 12-04-2018 23:37:15
98 1477370 13-04-2018 00:18:30
101 1477370 13-04-2018 00:18:30
102 1477370 13-04-2018 00:18:30
103 1477371 13-04-2018 05:16:03
104 1477554 13-04-2018 06:37:15
105 1478454 13 -04-2018 07:37:15
106 9354 13-04-2018 08:37:15
107 1480254 13-04-2018 09:37:15
108 1481154 13-04 -2018 10:37:15
109 1482053 13-04-2018 11:37:15
110 1482953 13-04-2018 12:37:15
111 1483853 13-04-2018 13:37:15
112 1484753 13-04-2018 14:37:15
113 1485614 13-04-2018 15:37:15
114 1486094 13-04-2018 16： 37:15
115 1486933 13-04-2018 17:37:15
116 1487833 13-04-2018 18:37:15
117 148 8733 13-04-2018 19:37:15
118 1489633 13-04-2018 20:37:15
119 1490533 13-04-2018 21:37:15
120 1491403 13 -04-2018 22:37:15
121 1492292 13-04-2018 23:37:15
122 1493186 14-04-2018 00:37:15
1035 1728982 21-05 -2018 05:21:43
1036 1728982 21-05-2018 05:21:43
1333 1736122 01-06-2018 23:56:45
我尝试了什么：
i尝试首先过滤以下记录：
SELECT T1。* FROM（SELECT DISTINCT（DATE_TIME）AS D1，按DATE_TIME DESC从订单读取R1）作为T1
i不知道如何处理：'（
解决方案
Hello experts,
I need your expert guidance to complete this query.
i have a table named 'FO' which have 3 columns ID, Reading, Date_time
I don't know from where data gets inserted, but it is vulnerable :(
there are many duplicate data with different ID(may be treat as primary column).
My task is to first eliminate duplicate rows(with same Date_time & Reading but with different ID) then subtract 'Reading' from previous record, if previous 'Reading' have smaller or equal to current. If current 'Reading' is smaller than previous then current will be treat as result with in a given date range.
sample data:
ID Reading Date_Time 7 1465564 09-04-2018 03:31:52 8 1465564 09-04-2018 03:31:52 13 1465564 09-04-2018 10:53:09 14 1465564 09-04-2018 10:53:09 15 1465568 09-04-2018 13:35:11 16 1465568 09-04-2018 13:35:11 20 1465568 09-04-2018 18:37:01 21 1465595 09-04-2018 19:37:15 22 1465601 09-04-2018 19:37:38 23 1465601 09-04-2018 19:37:38 24 1465608 09-04-2018 22:24:11 30 1465608 09-04-2018 22:24:11 31 1465809 10-04-2018 05:32:25 35 2832 10-04-2018 09:07:15 43 1465833 10-04-2018 09:42:53 44 1466228 10-04-2018 18:37:15 45 1466301 10-04-2018 19:22:47 46 6322 10-04-2018 20:36:32 47 1466331 10-04-2018 21:22:51 48 1466361 10-04-2018 22:36:15 49 1466376 10-04-2018 23:37:13 53 1469714 11-04-2018 03:37:15 54 9365 11-04-2018 04:37:15 55 1471514 11-04-2018 05:37:15 56 1472185 11-04-2018 06:21:59 61 1472192 11-04-2018 11:19:07 62 1472192 11-04-2018 11:19:07 63 2192 11-04-2018 13:04:17 68 1472208 11-04-2018 18:23:41 69 1472212 11-04-2018 19:20:27 70 1472212 11-04-2018 19:20:27 71 1472212 11-04-2018 19:20:27 72 1472212 11-04-2018 19:20:27 73 1472212 11-04-2018 19:20:27 74 1472443 12-04-2018 00:37:15 75 1473078 12-04-2018 01:37:15 76 1473978 12-04-2018 02:37:15 77 1474299 12-04-2018 03:37:13 78 1474301 12-04-2018 03:42:45 79 5182 12-04-2018 05:37:15 80 1476082 12-04-2018 06:37:15 81 1476242 12-04-2018 06:47:55 96 1476242 12-04-2018 06:47:55 97 1476752 12-04-2018 23:37:15 98 1477370 13-04-2018 00:18:30 101 1477370 13-04-2018 00:18:30 102 1477370 13-04-2018 00:18:30 103 1477371 13-04-2018 05:16:03 104 1477554 13-04-2018 06:37:15 105 1478454 13-04-2018 07:37:15 106 9354 13-04-2018 08:37:15 107 1480254 13-04-2018 09:37:15 108 1481154 13-04-2018 10:37:15 109 1482053 13-04-2018 11:37:15 110 1482953 13-04-2018 12:37:15 111 1483853 13-04-2018 13:37:15 112 1484753 13-04-2018 14:37:15 113 1485614 13-04-2018 15:37:15 114 1486094 13-04-2018 16:37:15 115 1486933 13-04-2018 17:37:15 116 1487833 13-04-2018 18:37:15 117 1488733 13-04-2018 19:37:15 118 1489633 13-04-2018 20:37:15 119 1490533 13-04-2018 21:37:15 120 1491403 13-04-2018 22:37:15 121 1492292 13-04-2018 23:37:15 122 1493186 14-04-2018 00:37:15 1035 1728982 21-05-2018 05:21:43 1036 1728982 21-05-2018 05:21:43 1333 1736122 01-06-2018 23:56:45
What I have tried:
i tried to first filter the records with following:
SELECT T1.* FROM (SELECT DISTINCT(DATE_TIME) AS D1, READING AS R1 FROM FO ORDER BY DATE_TIME DESC) AS T1
i don't know how to proceed with this :'(
解决方案
这篇关于从条件中减去当前的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！