搜索
搜索

SQL语句中的SQL Math

Damon 发表于 Dev
24
达蒙

我创建了一个查询来解决此问题。

我有一个具有这样结构的数据库

+-----+---------------------+---------+---------+
| id  | since               | idUsers | km      |
+-----+---------------------+---------+---------+
| 124 | 2005-07-18 15:00:00 |       1 | 25798.0 |
| 127 | 2005-07-19 18:00:00 |       3 | 25891.7 |
| 128 | 2005-07-20 00:00:00 |       3 | 25970.2 |
| 129 | 2005-07-18 12:00:00 |       3 | 25795.0 |
| 136 | 2005-07-19 15:00:00 |       1 | 25852.0 |
| 137 | 2005-07-23 12:00:00 |       1 | 26143.6 |
| 139 | 2005-08-07 00:00:00 |       1 |    NULL |
| 140 | 2005-08-21 00:00:00 |       1 |    NULL |
| 146 | 2005-07-27 15:00:00 |       2 | 26164.0 |
| 147 | 2005-07-28 15:00:00 |       2 | 26178.9 |
| 151 | 2005-08-17 13:00:00 |       1 | 26245.0 |
| 149 | 2005-08-08 18:00:00 |       1 | 26204.9 |
| 150 | 2005-08-10 15:00:00 |       1 | 26221.4 |
| 155 | 2005-08-27 19:00:00 |       1 |    NULL |
| 154 | 2005-08-29 09:00:00 |       2 | 26438.3 |
| 156 | 2005-08-28 00:00:00 |       1 |    NULL |
| 157 | 2005-08-29 14:00:00 |       3 | 26468.3 |
| 158 | 2005-09-07 15:00:00 |       2 | 26504.2 |
| 159 | 2005-09-05 13:00:00 |       1 | 26478.5 |
| 160 | 2005-09-02 13:00:00 |       1 | 26473.8 |
| 161 | 2005-09-25 12:00:00 |       3 | 26550.4 |
| 162 | 2006-04-02 13:00:00 |       3 | 26599.0 |
| 163 | 2006-04-14 18:00:00 |       1 |    NULL |
| 164 | 2006-04-15 00:00:00 |       1 |    NULL |
| 169 | 2006-04-16 00:00:00 |       1 | 26703.3 |
| 166 | 2006-04-29 12:00:00 |       1 |    NULL |
| 168 | 2006-04-30 00:00:00 |       1 |    NULL |
| 170 | 2006-04-16 08:00:00 |       3 | 26709.0 |
| 175 | 2006-05-27 00:00:00 |       3 |    NULL |
| 176 | 2006-05-28 00:00:00 |       3 | 27072.0 |
| 177 | 2006-05-26 18:00:00 |       3 |    NULL |
| 178 | 2006-04-18 15:00:00 |       2 | 26751.0 |
| 179 | 2006-04-16 17:00:00 |       2 | 26726.0 |
| 181 | 2006-04-23 07:00:00 |       3 | 26775.0 |
| 182 | 2006-05-01 00:00:00 |       1 | 26932.4 |
| 183 | 2006-04-30 18:00:00 |       1 |    NULL |
| 184 | 2006-05-11 17:00:00 |       2 | 26988.0 |
| 186 | 2006-06-12 12:00:00 |       2 | 27092.0 |
| 187 | 2006-06-11 19:00:00 |       1 |    NULL |
| 188 | 2006-06-12 00:00:00 |       1 |    NULL |
| 189 | 2006-06-15 17:00:00 |       2 | 27134.0 |
| 191 | 2006-07-01 11:00:00 |       3 | 27199.0 |
| 192 | 2006-06-23 16:00:00 |       3 | 27162.0 |
| 193 | 2006-07-09 15:00:00 |       2 | 27211.0 |
| 194 | 2006-06-30 18:00:00 |       1 | 27183.0 |
| 195 | 2006-07-26 11:00:00 |       2 |    NULL |
| 196 | 2006-08-15 14:00:00 |       2 | 27351.0 |
| 197 | 2006-08-05 16:00:00 |       3 | 27338.0 |
| 198 | 2006-08-06 16:00:00 |       3 | 27341.0 |
| 199 | 2006-09-05 18:00:00 |       3 |    NULL |
| 201 | 2006-09-06 00:00:00 |       3 | 27506.0 |
| 202 | 2006-09-16 13:00:00 |       1 |    NULL |
| 203 | 2006-09-17 00:00:00 |       1 |    NULL |
| 204 | 2006-09-18 00:00:00 |       1 |    NULL |
| 205 | 2006-09-19 00:00:00 |       1 | 27568.6 |
| 206 | 2006-10-10 18:00:00 |       1 | 27569.4 |
| 207 | 2006-10-30 15:00:00 |       1 | 27581.5 |
| 209 | 2006-12-29 18:00:00 |       2 |    NULL |
| 216 | 2007-04-01 08:00:00 |       1 | 27725.0 |
| 227 | 2007-04-26 00:00:00 |       2 |    NULL |
| 214 | 2007-03-29 09:00:00 |       1 | 27675.5 |
| 215 | 2007-03-31 10:00:00 |       1 | 27689.2 |
| 225 | 2007-04-15 08:00:00 |       1 | 27880.3 |
| 224 | 2007-04-14 19:00:00 |       1 | 27800.0 |
| 223 | 2007-04-14 12:00:00 |       1 | 27775.0 |
| 228 | 2007-04-26 23:00:00 |       2 |    NULL |
| 229 | 2007-04-27 00:00:00 |       2 | 28000.4 |
| 230 | 2007-04-28 00:00:00 |       2 |    NULL |
| 231 | 2007-04-25 12:00:00 |       2 |    NULL |
| 232 | 2007-04-20 18:00:00 |       1 | 27906.3 |
| 233 | 2007-04-22 15:00:00 |       1 | 27928.6 |
| 234 | 2007-04-21 17:00:00 |       1 | 27915.2 |
| 236 | 2007-04-23 19:00:00 |       1 | 28068.0 |
| 237 | 2007-05-01 07:00:00 |       3 | 28103.0 |
| 238 | 2007-04-28 18:00:00 |       1 | 28030.3 |
| 239 | 2007-05-20 11:00:00 |       1 | 28174.2 |
| 240 | 2007-05-20 15:00:00 |       2 | 28181.0 |
| 241 | 2007-05-23 16:00:00 |       2 | 28198.0 |
| 242 | 2007-06-03 16:00:00 |       2 | 28238.0 |
| 243 | 2007-06-10 09:00:00 |       3 |    NULL |
| 244 | 2007-07-01 12:00:00 |       2 | 28291.0 |
| 245 | 2007-07-07 15:00:00 |       2 | 28343.0 |
| 246 | 2007-07-30 15:00:00 |       1 | 28354.8 |
| 247 | 2007-07-31 12:00:00 |       1 | 28476.9 |
| 248 | 2007-08-01 15:00:00 |       1 |    NULL |
| 249 | 2007-08-04 11:00:00 |       3 |    NULL |
| 250 | 2007-08-05 00:00:00 |       3 |    NULL |
| 251 | 2007-08-05 10:00:00 |       3 |    NULL |
| 252 | 2007-08-06 00:00:00 |       3 | 28708.0 |
| 253 | 2007-08-09 17:00:00 |       1 | 28729.9 |
| 254 | 2007-08-24 19:00:00 |       2 | 28806.0 |
| 255 | 2007-09-01 10:00:00 |       2 | 28812.0 |
| 256 | 2007-09-16 13:00:00 |       3 | 28842.0 |
| 257 | 2007-10-16 02:00:00 |       2 |    NULL |
| 258 | 2008-06-19 12:00:00 |       1 |    NULL |
| 259 | 2008-06-20 00:00:00 |       1 |    NULL |
| 260 | 2008-06-21 00:00:00 |       1 |    NULL |
| 261 | 2008-06-22 00:00:00 |       1 |    NULL |
| 262 | 2008-06-23 00:00:00 |       1 |    NULL |
| 263 | 2008-06-24 00:00:00 |       1 | 28986.7 |
| 264 | 2008-06-26 18:00:00 |       2 | 28997.0 |
| 265 | 2008-06-29 13:00:00 |       2 | 28998.0 |
| 266 | 2008-06-29 18:00:00 |       3 | 29012.0 |
| 267 | 2008-07-08 16:00:00 |       2 | 29019.0 |
| 268 | 2008-07-28 18:00:00 |       2 | 29082.0 |
| 269 | 2008-08-10 15:00:00 |       2 | 29192.0 |
| 270 | 2008-08-21 17:00:00 |       2 |    NULL |
| 271 | 2008-08-24 13:00:00 |       2 | 29281.0 |
| 272 | 2008-08-27 21:00:00 |       3 |    NULL |
| 273 | 2008-08-28 00:00:00 |       3 |    NULL |
| 274 | 2008-08-29 00:00:00 |       3 |    NULL |
| 275 | 2008-08-30 00:00:00 |       3 | 29343.0 |
| 276 | 2008-08-30 18:00:00 |       2 | 29352.0 |
| 277 | 2008-09-05 17:00:00 |       2 | 29385.0 |
| 279 | 2008-10-10 14:00:00 |       1 |    NULL |
| 280 | 2008-10-11 00:00:00 |       1 |    NULL |
| 281 | 2008-10-12 00:00:00 |       1 |    NULL |
| 282 | 2008-10-13 00:00:00 |       1 | 29459.0 |
| 283 | 2009-04-05 10:00:00 |       2 | 29460.0 |
| 286 | 2009-04-19 10:00:00 |       3 | 29471.0 |
| 285 | 2009-05-02 00:00:00 |       1 |    NULL |
| 287 | 2009-04-22 15:00:00 |       2 | 29486.0 |
| 288 | 2009-05-01 10:00:00 |       1 |    NULL |
| 290 | 2009-05-20 15:00:00 |       3 |    NULL |
| 289 | 2009-05-03 00:00:00 |       1 | 29668.6 |
| 291 | 2009-05-21 00:00:00 |       3 |    NULL |
| 292 | 2009-05-22 00:00:00 |       3 |    NULL |
| 293 | 2009-05-23 00:00:00 |       3 |    NULL |
| 294 | 2009-05-22 09:00:00 |       3 |    NULL |
| 295 | 2009-05-24 00:00:00 |       3 | 29820.0 |
| 296 | 2009-05-31 15:00:00 |       2 | 29830.0 |
| 297 | 2009-06-07 16:00:00 |       1 | 29850.3 |
| 298 | 2009-07-05 00:00:00 |       1 |     0.0 |
| 299 | 2009-06-28 12:00:00 |       2 | 29895.0 |
| 300 | 2009-07-04 17:00:00 |       2 | 29977.0 |
| 301 | 2009-06-29 16:00:00 |       2 | 29935.0 |
| 302 | 2009-07-11 19:00:00 |       2 | 29991.0 |
| 303 | 2009-07-26 17:00:00 |       2 | 29993.0 |
| 304 | 2009-07-27 00:00:00 |       3 | 30138.0 |
| 305 | 2009-08-04 18:00:00 |       3 | 30200.0 |
| 306 | 2009-08-05 00:00:00 |       3 | 30205.0 |
| 307 | 2009-08-06 00:00:00 |       3 | 30219.0 |
| 308 | 2009-08-15 09:00:00 |       3 |    NULL |
| 309 | 2009-08-15 20:00:00 |       2 | 30357.0 |
| 310 | 2009-09-06 12:00:00 |       2 | 30408.0 |
| 311 | 2009-09-07 17:00:00 |       2 | 30453.0 |
| 312 | 2009-09-11 11:00:00 |       2 |     0.0 |
| 313 | 2009-10-25 08:00:00 |       1 | 30531.7 |
| 314 | 2009-10-01 19:00:00 |       1 |     0.0 |
| 317 | 2010-04-07 18:00:00 |       2 | 30571.0 |
| 316 | 2010-03-31 10:00:00 |       1 |     0.0 |
| 318 | 2010-04-18 12:00:00 |       2 | 30640.0 |
| 319 | 2010-04-29 13:00:00 |       1 | 30665.5 |
| 320 | 2010-05-21 16:00:00 |       3 |    NULL |
| 321 | 2010-05-22 00:00:00 |       3 | 30752.6 |
| 322 | 2010-05-26 19:00:00 |       1 | 30762.4 |
| 323 | 2010-05-29 18:00:00 |       1 | 30810.8 |
| 344 | 2010-07-19 16:00:00 |       1 | 31062.7 |
| 348 | 2010-08-22 13:00:00 |       2 |    NULL |
| 347 | 2010-08-08 09:00:00 |       2 | 31567.0 |
| 346 | 2010-07-25 15:00:00 |       1 | 31485.5 |
| 345 | 2010-07-20 11:00:00 |       1 |     0.0 |
| 343 | 2010-07-21 00:00:00 |       1 | 31382.5 |
| 334 | 2010-06-07 14:00:00 |       2 | 30837.0 |
| 335 | 2010-06-24 18:00:00 |       2 | 30853.0 |
| 336 | 2010-07-01 15:00:00 |       2 |    NULL |
| 337 | 2010-07-03 07:00:00 |       2 | 30948.0 |
| 338 | 2010-07-13 12:00:00 |       1 |    NULL |
| 339 | 2010-07-13 19:00:00 |       1 |    NULL |
| 340 | 2010-07-12 16:00:00 |       1 |    NULL |
| 341 | 2010-07-11 21:00:00 |       1 |    NULL |
| 349 | 2010-08-22 13:00:00 |       2 | 31682.0 |
| 350 | 2010-09-05 06:00:00 |       2 | 31724.0 |
| 351 | 2010-09-19 14:00:00 |       1 | 31772.4 |
+-----+---------------------+---------+---------+

现在，我必须找出哪个用户开车了几公里。 

Select r.idUsers, SUM(r.km) AS KM_TOTAL FROM Reservations r GROUP BY r.idUsers

但问题是km字段中的值现在是汽车的价值，因此基本上我必须找出下一行与当前行的id之间的区别（id127.km-id124.km），但是我刚开始编程，我不知道该如何解决

最终结果需要显示如下：

idUsers | TOTAL_KM
1       | (Total value of his km)
2       | (Total value of his km)
3       | (Total value of his km)

我希望你可以帮助我

编辑：

SELECT tt.iduser,sum(tt.next_km - tt.km) as TOTAL_KM
FROM (
SELECT t.iduser,t.km,
       (SELECT s.km FROM Reservations s
        WHERE s.since > t.since
        ORDER BY s.since
        LIMIT 1) as next_km
FROM Reservations t) tt
WHERE tt.next_km is not null
GROUP BY tt.iduser

给出输出

idusers | TOTAL_KM
1 | 62141.9 
2 | -59284.9 
3 | 596.9
萨加斯

您可以使用相关查询来获取下一个查询，KM然后对每个用户求和：

SELECT tt.iduser,sum(tt.next_km - tt.km) as TOTAL_KM
FROM (
    SELECT t.iduser,t.km,
           (SELECT s.km FROM Reservations s
            WHERE s.since > t.since
            ORDER BY s.since
            LIMIT 1) as next_km
    FROM Reservations t) tt
WHERE tt.next_km is not null
  AND tt.km is not null
GROUP BY tt.iduser

如果您的RDBMS的SQL-Server使用的是TOP 1，如果MySQL的使用的LIMIT 1，如果您还有其他名称，则没有标记您的RDBMS，请告诉我，我将对其进行调整。

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章

SQL语句中的SQL语句

SQL语句中的逻辑AND

SQL在语句中添加

PYODBC sql 语句中的 with 语句

SQL语句中的定义值

SQL 语句中的 Unique 与 MAX

在 SQL 语句中计数

SELECT语句中的sql concat

SQL 未在 if 语句中更新

SQL语句中的MUMPS $ Functions

WHERE子句中的SQL IF语句

JOIN语句中的SQL问题

AND语句中的SQL子查询

PHP语句中的SQL变量

在SQL语句中的CIKE内部？

大于在SQL CASE语句中

PHP：sql语句中的连接

从 SQL 语句中获取空集

SQL语句where子句中的case语句

分组以在SQL语句中包含case语句

我的SQL select语句中的case语句

SQL语句中的语法错误问题

（+）=“ +等于”运算符在SQL语句中

在 sql 语句中收到空值

如何比较 SQL 语句中的修剪变量？

Sql Server 截断语句中的问题

SQL Server：case语句中的昂贵查询

WHERE子句中嵌套SQL语句的结果

合并2 if SQL SELECT语句中的条件

TOP 榜单

  1. 1

    隐藏发件人没有短信PHP

  2. 2

    材质UI垂直滑块。如何改变在垂直材料UI滑块导轨的厚度（反应）

  3. 3

    在Windows 7中无法删除文件（2）

  4. 4

    HttpClient中的角度变化检测

  5. 5

    Azure VM启动/停止日志

  6. 6

    如何在 Vb.net 中使用函数返回多个值

  7. 7

    Powerpoint-条形长度错误的堆积条形图

  8. 8

    最新歌剧断断续续的快速拨号和渲染错误

  9. 9

    Mac OS X更新后的GRUB 2问题

  10. 10

    需要公式以vlookup逗号分隔单个单元格中的值

  11. 11

    Hashchange事件侦听器在将事件处理程序附加到事件之前进行侦听

  12. 12

    ggplot：对齐多个分面图-所有大小不同的分面

  13. 13

    OS X-为什么我需要打开WiFi才能确定最近的位置

  14. 14

    用日期数据透视表和日期顺序查询

  15. 15

    Java Eclipse中的错误13，如何解决？

  16. 16

    如何在Django中使用UUID

  17. 17

    加载Microsoft Visual菜单时出现问题

  18. 18

    具有if条件的SQL UPDATE

  19. 19

    从JSON到JSONL的Python转换

  20. 20

    如何在Kod中更改字体？

  21. 21

    共享图像将路径放入地址

热门标签

归档