如何查看年度最佳出席员工

Question

这是我的MySQL表:

_______________________________________________
Id | Name| Employee id | Date       | Attendance
_______________________________________________
1  | xyz |     196     | 2013-04-01 | present  
2  | xyz |     196     | 2013-04-02 | present  
3  | xyz |     196     | 2013-04-03 | present  
4  | xyz |     196     | 2013-04-04 | absent  
5  | xyz |     196     | 2013-04-05 | present  
6  | abc |     197     | 2013-04-01 | present
7  | abc |     197     | 2013-04-02 | present  
8  | abc |     197     | 2013-04-03 | present  
9  | abc |     197     | 2013-04-04 | present  
10 | abc |     197     | 2013-04-05 | present
_______________________________________________

我想计算员工出席的天数，我想在PHP中得到这样的结果:

___________________________________________________________
 Name| Employee id| Attendance     | Best OR NOT
___________________________________________________________
 xyz |  196       | 4 Present days |
 abc |  197       | 5 Present days | This is best employees of the year
__________________________________________________________________________

我该怎么做?

Answer 1

SQL

SELECT name, employeeID count(userID) as datesAttend FROM Attendance GROUP BY name

PHP (mysql类方法，只是一个例子)

/* mysql_query example ( use beter db class ) */
$best = 0;
$bestEmployee = '';
$query = mysql_query("SELECT Name, EmployeeID, count(EmployeeID) as datesAttend FROM Attendance GROUP BY EmployeeID");
while($row = mysql_fetch_array($query)) {
  echo $row['Name']. " " .$row['EmployeeID']. " " .$row['datesAttend'];
  if($row['datesAttend'] > $best) {
   $best = $row['datesAttend'];
   $bestEmployee = $row['EmployeeID'];
  }
}
echo $bestEmployee ." is employee of the year!!!";
throw PartyForEmployee();

的例子:http://www.sqlfiddle.com/!2/9d282/2/0

Answer 2

这样的查询:

select employeeId, count(employeid) as num from table_name group by attendence limit 1 

会给你最好的工人id。您可以通过将表插入到适当的位置来编辑该表。你能再解释一下你的问题吗?

//DB_HOST etc. are db connection constants
$dbc=mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
$data = mysql_query('your query') or die(mysql_error());
// A COUNT query will always return 1 row
// Use fetch_assoc it's easier to use
$row = mysql_fetch_assoc($data);
$bestId = $row['employeeId'];
$bestDayNumber = $row['num'];

Answer 3

此查询将为您提供最佳员工:

select Name, "Employee id", count(*) 
from table_name 
where Attendance = "present" 
group by Name 
order by count(*) desc 
limit 1;

"group by"子句意味着您希望为每个员工提供单独的结果。"where"从句确保你不计算他们不在的天数。"按顺序"条款将上班天数最多的员工放在第一位。"limit"子句只得到第一个，因为您只想要最好的。

Answer 4

试试这个查询。

SELECT *, COUNT(employee_id) AS cnt, CASE WHEN employee_id = (SELECT employee_id FROM employees
WHERE 1 GROUP BY  employee_id ORDER BY COUNT(employee_id) DESC LIMIT 0 , 1)   THEN 'Best' 
ELSE 'Not' END AS avl
FROM  `employees`
WHERE 1 
GROUP BY employee_id

根据需要修改表名和字段名

Answer 5

试试这个

   $query = mysql_query("select name ,`Employee id` as emplyee_id, CONCAT(count(`Employee id`),' Present days') as Attendance
   from Table1 
   where `Attendance` = 'present' group by `Employee id` ");

   while($row = mysql_fetch_array($query)) {
  echo $row['name']. " - " .$row['emplyee_id']. " - " .$row['Attendance']."</ br>";
 }

演示