我有一个这样的休假表:
**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;
}
你能帮我纠正一下吗?还是有更好的方法来计算休假?
你必须
在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 |
+------------+--------+------+---------------------------+
小提琴: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 |
+------------+-----------+
小提琴:http://sqlfiddle.com/#!9/5bac3/6