如何为表中的每一行运行特定的sql查询? [英] How to run a specific sql query for every row in the table?
问题描述
所以我的数据库中有两个表，它们都看起来像这样:
So I have two tables in my Database and they both look something like this:
通信:(已拨打电话)
Timestamp FromIDNumber ToIDNumber GeneralLocation
2012-03-02 09:02:30 878 674 Grasslands
2012-03-02 11:30:01 456 213 Tundra
2012-03-02 07:02:12 789 654 Mountains
运动:
Timestamp IDNumber Type X Y
2012-03-02 11:02:30 379 pedestrian 32 46
2012-03-01 12:32:41 654 pedestrian 54 56
2012-03-02 07:02:16 789 pedestrian 39 52
我想要做的是找到最接近给定通信时间戳的移动"时间戳.基本上，我想找到一个人打电话时可能去过的地方.
What I want to do is to find the closest Movement timestamp to a given Communication timestamp. Basically, I want to find where a person might have been when he/she made a call.
目前，我是这样做的:
SELECT
movement.timestamp,
movement.x,
movement.y
FROM movement
WHERE (movement.IDNumber=789 AND movement.timestamp>='2012-03-02 07:02:12')
ORDER BY movement.timestamp LIMIT 1;
，输出将是:
timestamp x y
2012-03-02 07:02:12 39 52
在其中将IDNumber和timestamp设置为呼叫者的FromIDNumber的地方，并且timestamp进行了呼叫.因此，这将找到一个呼叫者的可能位置.
Where I set the IDNumber and timestamp to the FromIDNumber of the caller, and timestamp they made the call. So, that'll find the probable location of one caller.
在这种情况下，789在07:02:12进行了呼叫，并且在07:02:16有他的运动数据，表明他的位置在(39,52).这样一来，它就会显示在他可能的位置，因为只有4秒钟之后.
In this case, 789 made a call at 07:02:12, and there's movement data for him at 07:02:16 which says his position is at (39,52). So that would show up as his probable location, seeing as it's only 4 seconds later.
但是，如何在Communication表中为每个呼叫运行该特定查询，而不必手动填写每个查询?
But how do I run that specific query for every single call in the Communication table without having to fill each in one by hand?
例如，然后，将为通信表的所有行计算位置，而不是仅针对一次呼叫进行计算，例如:
For example then, instead of only being calculated for 1 call, the location would be calculated for all the rows of the communication table like:
timestamp x y
2012-03-02 07:02:12 39 52
2012-03-02 03:15:14 23 34
2012-03-02 05:06:07 34 55
2012-03-02 02:41:21 12 56
...
，依此类推.
我尝试通过 java.sql使用某些东西，但是事情变得有点复杂，我想知道是否有更简单的方法.我该如何解决这个问题?
I tried using something with java.sql but things got a bit complicated, and I wondered if there was an easier way. How would I solve this issue?
问题是我刚开始学习SQL，所以我真的不确定在这里做什么.我看了一些其他的SO帖子，但它们全都用于sql-server，而我却真的听不懂.任何帮助将不胜感激，谢谢！
The problem is that I just started learning SQL so I'm not really sure what to do here at all. I looked at some other SO posts, but they were all for sql-server, and I wasn't really able to understand. Any help would be greatly appreciated thank you!
推荐答案
尝试以下方法:
SELECT c.FromIDNumber, m.Timestamp, m.X, m.Y
FROM Communication c
JOIN Movement m ON c.FromIDNumber = m.IDNumber
WHERE m.TimeStamp = (SELECT MIN(mm.Timestamp)
FROM Movement mm
WHERE mm.TimeStamp >= c.TimeStamp)
您可以使用此 SQL小提琴 进行测试不同的数据组合.
You can use this SQL Fiddle to test with different data combinations.
这篇关于如何为表中的每一行运行特定的sql查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
