来自多行的 MySQL 条件 [英] MySQL Conditions from Multiple Rows
问题描述
我一直在尝试使用 SELECT 语句计算表.我有一张这样的桌子:
I have been trying to calculate a table using a SELECT statement. I have a table like this:
--------------------------------------------------------------
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
我试图在选择期间进行计算并可能创建一个视图，但到目前为止还没有运气.最终表格将如下所示.
I was trying to do the calculations on during the select and possibly create a view but have no luck so far. In the end the table will look like this.
--------------------------------------------------------------
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 中但变得太大而无法管理，我们需要将其转移到性能更好的地方.
The total column uses values from the previous row to calculate its values. For example the date in row 4 will need to reference the date in row 3 to see if the date is greater. Would I need to try this with a subquery? How this will eventually work is that every 90 days up to 180 days the agent will loose 1 point if no more are incurred. Thus the reason why I need to reference other rows. If it helps this data is currently in Excel but is getting too large to manage and we need to move it over to something that performs better.
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;
推荐答案
是的，您需要使用子查询执行此操作，请尝试使用以下方法(如果您希望第 4 行访问第 3 行中的日期):
Yes, you need to do this with a subquery, try with something like this (if you want the 4th row to access the date in 3rd row):
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
您可以在此处
或者，如果您希望第 3 行可以访问第 4 行中的日期，则可以尝试此查询
Or you can try this query if you want that 3rd row has access to date in 4th row
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;
您可以在此处
这篇关于来自多行的 MySQL 条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
