php - 从mysql数据库计算叶子

Question

我有一个这样的休假表:

**employeeId    action      type       effective_date**
   1            leave       paid        2016-01-15
   2            leave       unpaid      2016-01-16
   1            return      return       2016-01-17
   2            return      return       2016-01-17

我尝试了这个查询:

$query = (select * from table where employeeId = "$empID" && type = "$type");

此后我陷入困境；其实我想要:

where Empid = 1 && action = leave && type = paid orderby effective_date

$i=0;
$j=$i+1;
$leave =0;
while($row = mysqli_fetch_array($query)){

    $leave = $row['effective_date'][$i] - $row['effective_date'][$j];
    $i+1;
    $j+1;
    $leaves = $leaves+$leave;
} 

你能帮我纠正这个问题吗？或者有更好的方法来计算休假吗？

Answer 1

您必须在 ORDER 和 BY 之间留出空格:

SELECT * FROM `employee`
    WHERE `employeeId`='1'
    AND   `action` = 'leave'
    AND   `type` = 'paid'
ORDER BY  `effective_date` ASC

输出

+------------+--------+------+---------------------------+
| employeeId | action | type |            effective_date |
+------------+--------+------+---------------------------+
|          1 |  leave | paid | January, 15 2016 00:00:00 |
+------------+--------+------+---------------------------+

fiddle :http://sqlfiddle.com/#!9/5bac3/3

这会给你叶子的数量:

SELECT `employeeId`, COUNT(*) AS `NumLeaves` FROM `employee`
    WHERE `employeeId`='1'
    AND   `action` = 'leave'
    AND   `type` = 'paid'
GROUP BY `employeeId`
ORDER BY  `effective_date` ASC

输出

+------------+-----------+
| employeeId | NumLeaves |
+------------+-----------+
|          1 |         1 |
+------------+-----------+

fiddle :http://sqlfiddle.com/#!9/5bac3/6