SQL-在MySQL中遍历表的每一行? [英] SQL - Looping through ever row of table in mysql?
问题描述
所以我有2个表communication
和movement
.
communication
的列fromID
,timestamp
具有呼叫者的ID,以及进行呼叫的时间.然后,我有另一个表movement
,该表具有ID
,timestamp
,x
,y
,该表具有一个人的ID,其位置(x,y)
以及它们在该位置的时间.
communication
has columns fromID
,timestamp
that has ID of caller, and time the call was made. Then I have another table movement
that has ID
,timestamp
,x
,y
, that has the ID of a person, their location (x,y)
, and the time that they are at that location.
我想写一个看起来像这样的查询:
I want to write a query that looks something like this:
For every single row of communication(R)
SELECT * FROM movement m
WHERE m.ID = R.fromID && m.timestamp <= R.timestamp
ORDER BY timestamp
基本上,这是为给定的communication timestamp
找到最接近的movement timestamp
.之后,最终,我想基于movement
数据找到呼叫的位置(x,y)
.
Basically, what this is doing is finding the closest movement timestamp
for a given communication timestamp
. After that, eventually, I want to find the location (x,y)
of a call, based on the movement
data.
我该怎么做?我知道有一种基于集合的方法,但是我不想那样做.我查看了cursors
,但是我感到在那方面的表现很糟糕……所以无论如何,要循环执行此操作吗?我本质上是想遍历communication
的每一行,并得到结果...
How would I do this? I know there's a set based approach, but I don't want to do it that way. I looked into cursors
, but I get the feeling that the performance is terrible on that... so is there anyway to do this with a loop? I essentially want to loop through every single row of the communication
, and get the result...
我尝试过这样的事情:
DELMITER $$
CREATE PROCEDURE findClosestTimestamp()
BEGIN
DECLARE commRowCount DEFAULT 0;
DECLARE i DEFAULT 0;
DECLARE ctimestamp DEFAULT 0;
SELECT COUNT(*) FROM communication INTO commRowCount;
SET i = 0;
WHILE i < commRowCount DO
SELECT timestamp INTO ctimestamp FROM communication c
SELECT * FROM movement m
WHERE m.vID = c.fromID && m.timestamp <= R.timestamp
END$$
DELIMITER ;
但是我知道那是完全错误的……这是唯一的实现游标的方法吗?我只是在Internet上的任何地方都找不到这样的示例,并且我对SQL过程完全陌生.任何指导将不胜感激,谢谢!!
But I know thats completely wrong... Is the only way to do this cursors?? I just can't find an example of this anywhere on the internet, and I'm completely new to procedures in SQL. Any guidance would be greatly appreciated, thank you!!
推荐答案
让我们看看是否可以使用光标将您指向正确的方向:
Let's see if I can point you in the right direction using cursors:
delimiter $$
create procedure findClosestTimeStamp()
begin
-- Variables to hold values from the communications table
declare cFromId int;
declare cTimeStamp datetime;
-- Variables related to cursor:
-- 1. 'done' will be used to check if all the rows in the cursor
-- have been read
-- 2. 'curComm' will be the cursor: it will fetch each row
-- 3. The 'continue' handler will update the 'done' variable
declare done int default false;
declare curComm cursor for
select fromId, timestamp from communication; -- This is the query used by the cursor.
declare continue handler for not found -- This handler will be executed if no row is found in the cursor (for example, if all rows have been read).
set done = true;
-- Open the cursor: This will put the cursor on the first row of its
-- rowset.
open curComm;
-- Begin the loop (that 'loop_comm' is a label for the loop)
loop_comm: loop
-- When you fetch a row from the cursor, the data from the current
-- row is read into the variables, and the cursor advances to the
-- next row. If there's no next row, the 'continue handler for not found'
-- will set the 'done' variable to 'TRUE'
fetch curComm into cFromId, cTimeStamp;
-- Exit the loop if you're done
if done then
leave loop_comm;
end if;
-- Execute your desired query.
-- As an example, I'm putting a SELECT statement, but it may be
-- anything.
select *
from movement as m
where m.vID = cFromId and m.timeStamp <= cTimeStamp
order by timestampdiff(SECOND, cTimeStamp, m.timeStamp)
limit 1;
end loop;
-- Don't forget to close the cursor when you finish
close curComm;
end $$
delimiter ;
参考文献:
这篇关于SQL-在MySQL中遍历表的每一行?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!