MySQL对数据表的循环操作和时间段相关操作如何实现

Question

SELECT dt1, dt2 FROM(
SELECT
@dt1 as dt1, 
@dt1 := @dt1 + INTERVAL 6 MONTH, 
CASE 
    WHEN RIGHT(LEFT(@dt1,7),2) > "8" THEN
        CAST(CONCAT_WS('-',LEFT(@dt1,4),LEFT(@dt1,4)+1) AS CHAR(10000) CHARACTER SET utf8)
    WHEN RIGHT(LEFT(@dt1,7),2) <= "8" THEN
        CAST(CONCAT_WS('-',LEFT(@dt1,4)-1,LEFT(@dt1,4)) AS CHAR(10000) CHARACTER SET utf8)
END as xn,
CASE 
    WHEN RIGHT(LEFT(@dt1,7),2) <= 8 THEN
        "1"
    WHEN RIGHT(LEFT(@dt1,7),2) > 8 THEN    
        "2"
END as xq
CASE WHEN @dt1 <= @dt2 THEN @dt ELSE  @dt2 END as dt2
FROM( SELECT @dt1:=CAST(gzrq as date), @dt2:=CAST( DATE_ADD(jcrq, INTERVAL 6 MONTH) as date) FROM T_ZDGZ ORDER BY gzrq ASC ) _x,
(SELECT @dt1:=NULL, @dt2:=NULL) vars
WHERE @dt1 <= @dt2
) A

这段sql语句只会对T_ZDGZ 数据表的最后一条记录进行计算，应该如何做才能实现对T_ZDGZ 数据表的每一条记录都进行相关计算，各位学霸们有谁知道吗

Answer 1

SELECT dt1, dt2 FROM(
    SELECT
    @dt1 as dt1,
    @dt1 := @dt1 + INTERVAL 6 MONTH,
    CASE
        WHEN RIGHT(LEFT(@dt1,7),2) > "8" THEN
            CAST(CONCAT_WS('-',LEFT(@dt1,4),LEFT(@dt1,4)+1) AS CHAR(10000) CHARACTER SET utf8)
        WHEN RIGHT(LEFT(@dt1,7),2) <= "8" THEN
            CAST(CONCAT_WS('-',LEFT(@dt1,4)-1,LEFT(@dt1,4)) AS CHAR(10000) CHARACTER SET utf8)
    END as xn,
    CASE
        WHEN RIGHT(LEFT(@dt1,7),2) <= 8 THEN
            "1"
        WHEN RIGHT(LEFT(@dt1,7),2) > 8 THEN
            "2"
    END as xq,
    CASE WHEN @dt1 <= @dt2 THEN @dt1 ELSE  @dt2 END as dt2
    FROM(
        SELECT
        @dt1:=CAST(gzrq as date),
        @dt2:=CAST( DATE_ADD(jcrq, INTERVAL 6 MONTH) as date)
        FROM T_ZDGZ
        ORDER BY gzrq ASC
    ) _x,
    (SELECT @dt1:=NULL, @dt2:=NULL) vars
    WHERE @dt1 <= @dt2
) A