我应该在 SQL 过程中使用游标吗？

Question

我有一个包含计算机登录和注销事件的表。每行都是一个单独的事件，带有时间戳、计算机名称、登录或注销事件代码以及其他详细信息。我需要创建一个 SQL 过程来遍历该表并找到相应的登录和注销事件，并将新行插入到另一个包含计算机名称、登录时间、注销时间和持续时间的表中。

那么，我应该使用光标来执行此操作还是有更好的方法来执行此操作？数据库非常庞大，因此效率肯定是一个问题。任何建议的伪代码也很棒。

[编辑：从评论中提取]

源表：

History (
      mc_id
    , hs_opcode
    , hs_time
)

现有数据解释：

Login_Event  = unique mc_id, hs_opcode = 1, and hs_time is the timestamp
Logout_Event = unique mc_id, hs_opcode = 2, and hs_time is the timestamp

Answer 1

首先，如果您可以以不需要复杂的子查询来配对行的方式对数据进行排序，那么您的查询将会更简单（并且更快）。由于 MySQL 不支持 CTE 即时执行此操作，因此您需要创建一个临时表：

CREATE TABLE history_ordered (
  seq INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
  hs_id INT,
  mc_id VARCHAR(255),
  mc_loggedinuser VARCHAR(255),
  hs_time DATETIME,
  hs_opcode INT
);

然后，从原始表中提取并排序到新表中：

INSERT INTO history_ordered (
  hs_id, mc_id, mc_loggedinuser,
  hs_time, hs_opcode)
SELECT
  hs_id, mc_id, mc_loggedinuser,
  hs_time, hs_opcode
FROM history ORDER BY mc_id, hs_time;

您现在可以使用此查询来关联数据：

SELECT li.mc_id,
       li.mc_loggedinuser,
       li.hs_time as login_time,
       lo.hs_time as logout_time
FROM   history_ordered AS li
JOIN   history_ordered AS lo
  ON   lo.seq = li.seq + 1
   AND li.hs_opcode = 1;

对于将来的插入，您可以使用如下所示的触发器来自动更新持续时间表：

DELIMITER $
CREATE TRIGGER `match_login` AFTER INSERT ON `history`
FOR EACH ROW
BEGIN
 IF NEW.hs_opcode = 2 THEN
  DECLARE _user VARCHAR(255);
  DECLARE _login DATETIME;
  SELECT mc_loggedinuser, hs_time FROM history
  WHERE hs_time = (
   SELECT MAX(hs_time) FROM history
   WHERE hs_opcode = 1
   AND mc_id = NEW.mc_id
  ) INTO _user, _login;
  INSERT INTO login_duration
  SET machine = NEW.mc_id,
  logout = NEW.hs_time,
  user = _user,
  login = _login;
 END IF;
END$
DELIMITER ;

Answer 2

CREATE TABLE dummy (fields you'll select data into, + additional fields as needed)

INSERT INTO dummy (columns from your source)
SELECT * FROM <all the tables where you need data for your target data set>

UPDATE dummy SET col1 = CASE WHEN this = this THEN that, etc

INSERT INTO targetTable
SELECT all columns FROM dummy

如果没有您正在处理的任何代码.. 很难看出这种方法是否有用.. 可能在某些情况下您确实需要循环遍历事物.. 在某些情况下这种方法可以使用代替..

[编辑：基于海报的评论]

你可以尝试执行这个并看看你是否得到了想要的结果？

INSERT INTO <your_target_table_here_with_the_three_columns_required>
SELECT li.mc_id, li.hs_time AS login_time, lo.hs_time AS logout_time
FROM
    history AS li
    INNER JOIN history AS lo
        ON li.mc_id = lo.mc_id
            AND li.hs_opcode = 1
            AND lo.hs_opcode = 2
            AND lo.hs_time = (
                SELECT min(hs_time) AS hs_time 
                FROM history 
                WHERE hs_time > li.hs_time 
                AND mc_id = li.mc_id
            )