查看以显示一个周期距离当前周期有多远

Question

我有一个称为循环的表，该表具有以下结构：ID开始于end_at

ID不是唯一的，因此已注册：

 id        starts_at            ends_at          cycle_year   cycle_number
 120       jan-01-2017          jan-05-2017         2017          1
 120       jan-06-2017          jan-11-2017         2017          2
 120       jan-12-2017          jan-18-2017         2017          3

我需要做的是创建这样的视图：

 id        starts_at            ends_at           how_far_it_is_from_newest
 120       jan-01-2017          jan-05-2017             2
 120       jan-06-2017          jan-11-2017             1
 120       jan-12-2017          jan-18-2017             0

有什么想法？

我已经创建了这个，但是有一个问题：

SET @counter = -1;
SELECT 
    *,
    concat(year, LPAD(number, 3, '0')) as 'cycle_ref', // eg. 2017001
    (@counter:=@counter + 1) as counter
FROM
    cycle
where
    id = 120
ORDER by 
    cycle_ref DESC
;

它正确返回，但是如果我取下ID = 120，它将计算不管ID。

 id        starts_at            ends_at           how_far_it_is_from_newest
 120       jan-12-2017          jan-18-2017             0
 77        jan-16-2017          jan-28-2017             1 <-- wrong:should be 0
 120       jan-06-2017          jan-11-2017             2 <-- wrong:should be 1
 120       jan-01-2017          jan-05-2017             3 <-- wrong:should be 2

Answer 1

这对我有用：

DROP FUNCTION IF EXISTS initCycleIndex;
DELIMITER $$
CREATE FUNCTION initCycleIndex() returns INT
BEGIN
    SET @counter:=-1;
    RETURN(@counter);
END $$
DELIMITER ;

DROP FUNCTION IF EXISTS getCycleIndex;
DELIMITER $$
CREATE FUNCTION getCycleIndex() RETURNS INT
    DETERMINISTIC
BEGIN
    SET @counter:= @counter + 1;
    return (@counter);
END $$
DELIMITER ;

CREATE OR REPLACE VIEW vw_cycle_indexed as
SELECT 
    c.*,
    concat(c.year, LPAD(c.number, 3, '0')) as 'cycle_ref',
    getCycleIndex() as 'index'
FROM
    cycle c
JOIN (select initCycleIndex()) r
ORDER by 
    cycle_ref DESC
;

#SELECT * FROM vw_cycle_indexed where id = 178;

Answer 2

在此处解决的多个问题。

要初始化变量，您不需要功能。只需在子查询(或单独的集合语句中(进行。

最大问题：您尚未定义订单来确定最新条目是什么。您的"离最新"可能偶尔改变。

您所实现的只是行计数器。当然，无论ID是否完全不同，它都会继续计数。您必须具有另一个具有上一行值的变量。您可以这样做，通过将当前行的值分配给之后的变量>您已经检查了变量的值。因此，选择部分中的顺序非常重要。
IF()函数非常简单，当上一个行ID与当前行不同时，返回0，否则递增的行计数器。这就是为什么在ORDER子句中使用ID进行一阶的一阶也很重要的原因。

尝试一下：

SELECT 
    c.*,
    concat(year, LPAD(number, 3, '0')) as 'cycle_ref',
    @counter := IF(@prevID != id, 0, @counter + 1),
    @prevID := id
FROM
    cycle c
    , (SELECT @counter := -1, @prevID := NULL) var_init_subquery
ORDER by 
    id, ends_at DESC, cycle_ref
;

唯一的缺点是，您不能在视图中使用此语句，因为不允许使用变量。不过，我将尝试使用这些功能的方法。这可能是一个整洁的解决方法。