php - PHP - Mysqli 每月考勤日历
问题描述
这是我的考勤表。
id employee_name attStatus empdates
--------------------------------------------------------------------
1 Arun p 2020-03-15 09:30:28.120
2 Alex a 2020-03-16 09:30:28.120
3 Jimy p 2020-03-17 09:30:28.120
---------------------------------------------------------------------
我想要像下面这样的结果
我如何编写 mysqli 脚本来打印每个员工的每月出勤率?
解决方案
我得到了解决方案。代码有点长。
$first_day_this_month = date('Y-m-01');
$last_day_this_month = date('Y-m-t');
$sql = "SELECT eid,
GROUP_CONCAT(if(DAY(`empdates`) = 1, `attStatus`, NULL)) AS '01',
GROUP_CONCAT(if(DAY(`empdates`) = 2, `attStatus`, NULL)) AS '02',
GROUP_CONCAT(if(DAY(`empdates`) = 3, `attStatus`, NULL)) AS '03',
GROUP_CONCAT(if(DAY(`empdates`) = 4, `attStatus`, NULL)) AS '04',
GROUP_CONCAT(if(DAY(`empdates`) = 5, `attStatus`, NULL)) AS '05',
GROUP_CONCAT(if(DAY(`empdates`) = 6, `attStatus`, NULL)) AS '06',
GROUP_CONCAT(if(DAY(`empdates`) = 7, `attStatus`, NULL)) AS '07',
GROUP_CONCAT(if(DAY(`empdates`) = 8, `attStatus`, NULL)) AS '08',
GROUP_CONCAT(if(DAY(`empdates`) = 9, `attStatus`, NULL)) AS '09',
GROUP_CONCAT(if(DAY(`empdates`) = 10, `attStatus`, NULL)) AS '10',
GROUP_CONCAT(if(DAY(`empdates`) = 11, `attStatus`, NULL)) AS '11',
GROUP_CONCAT(if(DAY(`empdates`) = 12, `attStatus`, NULL)) AS '12',
GROUP_CONCAT(if(DAY(`empdates`) = 13, `attStatus`, NULL)) AS '13',
GROUP_CONCAT(if(DAY(`empdates`) = 14, `attStatus`, NULL)) AS '14',
GROUP_CONCAT(if(DAY(`empdates`) = 15, `attStatus`, NULL)) AS '15',
GROUP_CONCAT(if(DAY(`empdates`) = 16, `attStatus`, NULL)) AS '16',
GROUP_CONCAT(if(DAY(`empdates`) = 17, `attStatus`, NULL)) AS '17',
GROUP_CONCAT(if(DAY(`empdates`) = 18, `attStatus`, NULL)) AS '18',
GROUP_CONCAT(if(DAY(`empdates`) = 19, `attStatus`, NULL)) AS '19',
GROUP_CONCAT(if(DAY(`empdates`) = 20, `attStatus`, NULL)) AS '20',
GROUP_CONCAT(if(DAY(`empdates`) = 21, `attStatus`, NULL)) AS '21',
GROUP_CONCAT(if(DAY(`empdates`) = 22, `attStatus`, NULL)) AS '22',
GROUP_CONCAT(if(DAY(`empdates`) = 23, `attStatus`, NULL)) AS '23',
GROUP_CONCAT(if(DAY(`empdates`) = 24, `attStatus`, NULL)) AS '24',
GROUP_CONCAT(if(DAY(`empdates`) = 25, `attStatus`, NULL)) AS '25',
GROUP_CONCAT(if(DAY(`empdates`) = 26, `attStatus`, NULL)) AS '26',
GROUP_CONCAT(if(DAY(`empdates`) = 27, `attStatus`, NULL)) AS '27',
GROUP_CONCAT(if(DAY(`empdates`) = 28, `attStatus`, NULL)) AS '28',
GROUP_CONCAT(if(DAY(`empdates`) = 29, `attStatus`, NULL)) AS '29',
GROUP_CONCAT(if(DAY(`empdates`) = 30, `attStatus`, NULL)) AS '30',
GROUP_CONCAT(if(DAY(`empdates`) = 31, `attStatus`, NULL)) AS '31',
COUNT(if(`attStatus`='P', `attStatus`, NULL)) AS 'totalP',
COUNT(if(`attStatus`='A', `attStatus`, NULL)) AS 'totalA',
COUNT(if(`attStatus`='L', `attStatus`, NULL)) AS 'totalL'
FROM `attendance `
WHERE `empdates` BETWEEN '".$first_day_this_month."' AND '".$last_day_this_month."'
GROUP BY id";
推荐阅读
- c++ - 我无法在 Eclipse 中创建新的 C 项目:没有可用的项目类型。无法创建项目
- python - 为什么我的 Python 代码不能返回复数的实部?
- r - 按单词集拆分列,并用这些单词命名列
- latex - 数学符号左侧空间丢失
- python - 使用 Pyinstaller 后无法使用我的脚本运行子进程?
- c - C cs50 风格马里奥金字塔中的缩进
- java - Java:从 File 文件夹中读取并将每个添加到 ArrayList
- kubernetes - Kubernetes 工作节点必须具有完全相同的硬件和操作系统
- c# - Task.FromResult 的可空性警告
- php - Woocommerce:如何调试与新订单电子邮件相关的问题?