MySQL QUERY - 循环遍历表并执行条件计算（对于PHP）

问题描述：

I'm working on one project and now I'm in blind spot, trying to resolve this..

I have MySQL table with name x_world with some columns. Here is an example:

 internalid     name     population           date
          1     John            100     2014-05-01
          2    Tommy            250     2014-05-01
          3     Paul            200     2014-05-01
          4    Holly            700     2014-05-01
          5     John            100     2014-05-02
          6    Tommy            240     2014-05-02
          7     Paul            201     2014-05-02
          8    Holly            680     2014-05-02
          9     John            100     2014-05-03
         10    Tommy            230     2014-05-03
         11     Paul            202     2014-05-03
         12    Holly            799     2014-05-03

What i need, is get this result:

 name     population     (+-1d)     (+-3d)
 John            100         0          0
Tommy            230       -10        -20
 Paul            202        +1         +2

Here is explanation about what i mean and need:

Max allowed population change (grow) is 2 (including 2). If population grows over 2, dont display this row in result.
If population is only descending (no matter how much), it's okay, display this row.
Combination of both above conditions: if population descends one day (no matter how much), and then grows (no more then 2) - it's okay too, display this row.
If population does not change during these 3 days, it's okay, display this row.

In addition:

The value of population displayed in result equals to the last known value (latest date).
The two values (+-1d) and (+-3d) are calculated.

I'm beginner in PHP and MySQL. I'm familiar to very basics of both, however, the MySQL part looks insane for me :( I appreciate every piece of help. And finally, sorry for my bad english. I did my best :)

答

SELECT cur.name, cur.population,
       cur.population-d1.population AS `+-1d`, cur.population-d3.population AS `+-3d`
FROM (
    SELECT name, MAX(date) AS curdate
    FROM x_world
    GROUP BY name) AS maxes
JOIN x_world AS cur ON cur.name = maxes.name AND cur.date = maxes.curdate
JOIN x_world AS d1 ON d1.name = maxes.name AND d1.date = DATE_SUB(maxes.curdate, INTERVAL 1 DAY)
JOIN x_world AS d3 ON d3.name = maxes.name AND d3.date = DATE_SUB(maxes.curdate, INTERVAL 2 DAY)
HAVING `+-1d` <= 2 AND `+-3d` <= 2

DEMO

Note that although you call it +-3d, there's actually only 2 days between 2014-05-01 and 2014-05-03. I've kept your naming, but use INTERVAL 2 DAY in the calculation.

MySQL QUERY - 循环遍历表并执行条件计算（对于PHP）

相关推荐