mysql表格下一行快捷,如何从MySQL中的表中选择特定数据并从下一行可用的下一个日期减去它？...

+--------+------------+----------+

| animal | date | quantity |

+--------+------------+----------+

| dog | 2015-01-01 | 400 |

| cat | 2015-01-01 | 300 |

| dog | 2015-01-02 | 402 |

| rabbit | 2015-01-01 | 500 |

| cat | 2015-01-02 | 304 |

| rabbit | 2015-01-02 | 508 |

| rabbit | 2015-01-03 | 524 |

| rabbit | 2015-01-04 | 556 |

| rabbit | 2015-01-05 | 620 |

| rabbit | 2015-01-06 | 748 |

+--------+------------+----------+

Thanks to the users that contributed (in special https://stackoverflow.com/users/1816093/drew) now I am able to run the following query:

select

animal,

date,

quantity,

quantity_diff

from

( SELECT

a.animal,

a.Date AS actual_date,

past_date.Date AS date,

a.quantity AS quantity,

(a.quantity - past_date.quantity) AS quantity_diff,

1 as drewOrder

FROM inventory a

JOIN

(SELECT b.animal, b.date AS date1,

(SELECT MAX(c.date)

FROM inventory c

WHERE c.date < b.date AND c.animal = b.animal

GROUP BY c.animal)

AS date2

FROM inventory b)

AS original_date ON original_date.animal = a.animal

AND original_date.date1 = a.date

JOIN

inventory past_date

ON past_date.animal = a.animal

AND past_date.date = original_date.date2

union

select distinct null,animal,null,null,null,2 as drewOrder from inventory

) x

where x.animal='rabbit' group by quantity_diff

order by x.animal,x.drewOrder,x.actual_date;

This is what I get:

+--------+------------+----------+---------------+

| animal | date | quantity | quantity_diff |

+--------+------------+----------+---------------+

| rabbit | 2015-01-01 | 508 | 8 |

| rabbit | 2015-01-02 | 524 | 16 |

| rabbit | 2015-01-03 | 556 | 32 |

| rabbit | 2015-01-04 | 620 | 64 |

| rabbit | 2015-01-05 | 748 | 128 |

+--------+------------+----------+---------------+

...I am pretty close but i just can't get it like I want it; which is like this:

+--------+------------+----------+---------------+

| animal | date | quantity | quantity_diff |

+--------+------------+----------+---------------+

| rabbit | 2015-01-01 | 500 | 8 |

| rabbit | 2015-01-02 | 508 | 16 |

| rabbit | 2015-01-03 | 524 | 32 |

| rabbit | 2015-01-04 | 656 | 64 |

| rabbit | 2015-01-05 | 620 | 128 |

| rabbit | 2015-01-06 | 748 | null |

+--------+------------+----------+---------------+

I also should be able to change the "animal" and get its respective output:

cat:

+--------+------------+----------+---------------+

| animal | date | quantity | quantity_diff |

+--------+------------+----------+---------------+

| cat | 2015-01-01 | 300 | 4 |

| cat | 2015-01-02 | 304 | null |

+--------+------------+----------+---------------+

dog:

+--------+------------+----------+---------------+

| animal | date | quantity | quantity_diff |

+--------+------------+----------+---------------+

| dog | 2015-01-01 | 400 | 2 |

| dog | 2015-01-02 | 402 | null |

+--------+------------+----------+---------------+

解决方案

Well there is a simpler way to do it without that big query with joins and unions. You need an Window Function for your specific problem it would be a LEAD function.

LEAD(`quantity`) over (Partition By `date` order by `date`)

But unfortunately MySql doesn't support those functions so, you have to mimic it with query variables.

The solution as you want would be:

select animal,

`date`,

quantity,

lead-quantity quantity_diff

from ( select i.animal,

i.`date`,

@qt as lead,

@qt := i.quantity as quantity

from inventory i

where i.animal = 'rabbit'

order by `date` desc

) tab

order by `date`;

I was frustrated to know that MySql doesn't support the Window Functions until I find this article: Analytic functions: FIRST_VALUE, LAST_VALUE, LEAD, LAG which lead me to learn how to mimic it. I suggest you to favorite it as I did!

Hope it helps!