mysql - SQL在有条件的日期之间查找行

问题描述

这个查询需要一些帮助，我让它与 SP 一起工作，但它很慢，我根本无法使用它。

我有一个名为 cb_flights 的表，其中包含 -

pk      ucid        takeoff_time    end_time        end_event       kills
3321598 1828a3b47   15:17:21        15:17:39        eject           1
3324418 1828a3b47   15:18:12        15:42:24        landing         2
3341913 1828a3b47   19:00:06        19:00:57        pilot_death     0
3342956 1828a3b47   19:08:45        19:08:56        landing         0
3345329 1828a3b47   19:09:23        19:31:42        landing         2
3346649 1828a3b47   19:38:17        19:38:34        landing         0
3348150 1828a3b47   19:40:01        19:48:39        pilot_death     1

该表目前仅显示完整的 2 条生命。在 pk 3324418 - 3341913 之间有 2 次杀戮，在 pk 3342956 - 3348150 之间有 3 次杀戮。

我正在尝试获得连续飞行的开始和结束时间的结果-

ucid        streak_start        streak_end      sum(kills)
1828a3b47   15:18:12            19:00:57        2
1828a3b47   19:08:45            19:48:39        3

我需要上面的表格作为临时加入另一个表格并获得杀戮的描述。之后获得 ucid 的最佳连胜记录。

我有一个 SP 已经在没有与另一张桌子组合的情况下通过通用杀戮完成了最好的连胜，但它非常慢。SP -

CREATE DEFINER=`123`@`%` PROCEDURE `bestStreak`(in t_ucid VARCHAR(50))
BEGIN
DECLARE streakstart DATETIME;
DECLARE streakend DATETIME;
DECLARE streakresult INT;
DECLARE beststreak INT;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET streakresult = 0;

-- FROM FIRST SORTIE TO FIRST DEATH BEST STREAK
select min(takeoff_time) into streakstart
from cb_flights
where ucid = t_ucid;

select min(end_time) into streakend
from cb_flights
where end_event <> 'landing'
and ucid = t_ucid;

if streakresult is null then
    select sum(kills) into beststreak
    from cb_flights
    where takeoff_time >= streakstart 
    and takeoff_time < streakend
    and ucid = t_ucid
    group by ucid;
elseif streakresult = 0 then
    select sum(kills) into beststreak
    from cb_flights
    where takeoff_time >= streakstart 
    and ucid = t_ucid
    group by ucid;
end if;

-- FROM SECOND SORTIE TO LAST DEATH - EVALUATE ALL AND PUT IN BEST STREAK
beststrk: WHILE (streakstart<>streakend) DO

    select min(end_time) into streakstart
    from cb_flights
    where end_event <> 'landing'
    and end_time > streakstart
    and ucid = t_ucid;

    select min(end_time) into streakend
    from cb_flights
    where end_event <> 'landing'
    and end_time > streakstart
    and ucid = t_ucid;

    select sum(kills) into streakresult
    from cb_flights
    where takeoff_time between streakstart and streakend
    and ucid = t_ucid
    group by ucid;

    if streakresult > beststreak then
        select streakresult into beststreak;
    end if;        
END WHILE;

select beststreak;
END

感谢任何帮助！

编辑

添加 - 显示创建表 cb_flights

'CREATE TABLE `cb_flights` (
  `pk` int(11) NOT NULL AUTO_INCREMENT,
  `ucid` varchar(50) NOT NULL,
  `takeoff_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `end_event` varchar(45) DEFAULT NULL,
  `side` varchar(45) DEFAULT NULL,
  `kills` int(11) DEFAULT NULL,
  `type` varchar(45) DEFAULT NULL,
  `map_fk` int(11) DEFAULT NULL,
  `era_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`pk`),
  UNIQUE KEY `ucid_takeofftime` (`ucid`,`takeoff_time`),
  KEY `ucid_idx` (`ucid`) /*!80000 INVISIBLE */,
  KEY `end_event` (`end_event`) /*!80000 INVISIBLE */,
  KEY `side` (`side`)
) ENGINE=InnoDB AUTO_INCREMENT=7713276 DEFAULT CHARSET=utf8'

标签： mysqlsql

mysql - SQL在有条件的日期之间查找行

问题描述

解决方案

推荐阅读