mysql - 多行的MySQL条件

我一直在尝试使用SELECT语句计算表。我有一个这样的表：

--------------------------------------------------------------
AgentID     |      Date      |  Incurred     |    FallOffDate
==============================================================
kegomez     |   2012-11-19   |     2.0       |    2013-11-19
kegomez     |   2012-11-24   |     0.5       |    2013-11-24
kegomez     |   2013-01-21   |     2.0       |    2014-01-21
kegomez     |   2013-08-18   |     2.0       |    2014-08-18

我试图在选择过程中进行计算，可能会创建一个视图，但到目前为止还算不上运气。最后，表格将如下所示。

--------------------------------------------------------------
AgentID     |      Date      |  Incurred     |    90    |    180    |   Total    |    FallOffDate
==============================================================
kegomez |   2012-11-19   |     2.0       |    2.0   |    2.0    |   2.0      |    2013-11-19
kegomez |   2012-11-24   |     0.5       |    0.5   |    0.5    |   2.5      |    2013-11-24
kegomez |   2013-01-21   |     2.0       |    1.0   |    0.0    |   2.5      |    2014-01-21
kegomez |   2013-08-18   |     2.0       |    2.0   |    2.0    |   4.5      |    2014-08-18

总计列使用上一行中的值来计算其值。例如，第4行中的日期将需要引用第3行中的日期，以查看该日期是否更大。我需要使用子查询来尝试吗？最终的工作方式是，如果没有发生更多的情况，则代理将每90天到180天减少1分。因此，我之所以需要引用其他行。如果它有帮助，则该数据当前在Excel中，但由于太大而无法管理，我们需要将其移至性能更好的地方。

SELECT AgentID, Date, Incurred,
    @90 := IF(Date<=CURDATE()-90 AND @r=0, Incurred-1.0, IF(Difference>90, Incurred-1, Incurred)) AS 90Day,
    @180 := IF(Date<=CURDATE()-90 AND @r=0, Incurred-1.0, IF(Difference>180, Incurred-2, @90)) AS 180Day,

    @Total := IF(@180<0,0,IF(FallOffDate<=CURDATE(),0, @180)) AS Total,

    FallOffDate

FROM (SELECT  mo.AgentID, mo.Incurred, FallOffDate,
        @r AS LEAD_date,
        DATEDIFF(@r,Date) AS Difference,
        (@r := Date) AS Date

FROM    (
        SELECT  m.*
        FROM    (
                SELECT  @_date = NULL
                ) VARIABLE,
                attendance m
        ORDER BY
                AgentID, Date DESC
        ) mo
WHERE  (CASE WHEN @_date IS NULL OR @_date <> date THEN @r := NULL ELSE NULL END IS NULL)
        AND (@_date := date) IS NOT NULL) T
ORDER BY AgentID, Date;

最佳答案

是的，您需要使用子查询来执行此操作，请尝试使用类似的方法（如果您希望第四行访问第三行中的日期）：

SELECT  mo.AgentID, mo.date,
        @r AS 'LAG(date)',
        (case when @r<Date then 'YES' when @r is null then 'IS NULL' else 'NO' end) 'Is Bigger',
        (@r := Date) AS Date

FROM    (
        SELECT  m.*
        FROM    (
                SELECT  @_date = NULL
                ) variable,
                data m
        ORDER BY
                AgentID
        ) mo
WHERE  (CASE WHEN @_date IS NULL OR @_date <> date THEN @r := NULL ELSE NULL END IS NULL)
        AND (@_date := date) IS NOT NULL

您可以看到一个有效的演示here

或者，如果您希望第三行有权访问第四行中的日期，则可以尝试执行此查询

SELECT AgentID,date,LEAD_date,concat(Difference,' days') FROM
(SELECT  mo.AgentID,
        @r AS LEAD_date,
        DATEDIFF(@r,Date) as Difference,
        (@r := Date) AS Date

FROM    (
        SELECT  m.*
        FROM    (
                SELECT  @_date = NULL
                ) variable,
                data m
        ORDER BY
                AgentID,date desc
        ) mo
WHERE  (CASE WHEN @_date IS NULL OR @_date <> date THEN @r := NULL ELSE NULL END IS NULL)
        AND (@_date := date) IS NOT NULL) T
order by AgentID,date;

您可以看到一个有效的演示here

关于mysql - 多行的MySQL条件，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/18541451/