mysql - View 以显示一个循环与当前循环的距离

Question

我有一个名为 cycle 的表，它具有以下结构:ID开始于结束于

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

我需要做的是创建这样的 View :

 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;