本文介绍了获得缺席和存在的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
大家好...... i希望得到各个员工的总和(缺席)和总和(在场),下表结构澄清了我的问题...
--------------------------------- --------------- 日期 | Emp_id | Absents 或 Presence ----------------------------- ------------------- 1-1-2012 | 1 | P 2-1-2012 | 1 | P 3-1-2012 | 1 | A 4-1-2012 | 1 | P 1-1-2012 | 2 | P 2-1-2012 | 2 | A 3-1-2012 | 2 | A 4-1-2012 | 2 | P 1-1-2012 | 3 | P 2-1-2012 | 3 | P 3-1-2012 | 3 | P 4-1-2012 | 3 | P ________________________________________________1)在此表中有一个人员出勤表 2)P =存在。 A =缺席 我希望所有员工的存在和缺席次数如下所示
-------------------------------------------- - Emp_id |否。 .presence | .absents --------------------------- ------------------ 1 | 3 (总和 P)| 1 (Sum A) 2 | 2 | 2 3 | 4 | 0 ------------------------------- --------------我不知道是否有可能...... 请分享您的想法...
解决方案尝试: SELECT Emp_id, SUM( CASE WHEN AorP = ' P' 那么 1 ELSE 0 END ), SUM( CASE WHEN AorP = ' A' 那么 1 ELSE 0 END ) FROM MyTable GROUP BY Emp_Id
Hi all... i want get sum(absents) and sum(presence) for the individual employees,the following table structure is clarify my question...
------------------------------------------------ Date | Emp_id | Absents OR Presence ------------------------------------------------ 1-1-2012 | 1 | P 2-1-2012 | 1 | P 3-1-2012 | 1 | A 4-1-2012 | 1 | P 1-1-2012 | 2 | P 2-1-2012 | 2 | A 3-1-2012 | 2 | A 4-1-2012 | 2 | P 1-1-2012 | 3 | P 2-1-2012 | 3 | P 3-1-2012 | 3 | P 4-1-2012 | 3 | P ________________________________________________1)In this table has a emp attendance table 2)P=presence. A=absents I want the number of presence and absents the all employees like below
--------------------------------------------- Emp_id | No.of.presence | No.of.absents --------------------------------------------- 1 | 3(sum of P) | 1(Sum of A) 2 | 2 | 2 3 | 4 | 0 ---------------------------------------------I don't know it's possible or not... please share your ideas...
解决方案 Try: SELECT Emp_id, SUM(CASE WHEN AorP='P' THEN 1 ELSE 0 END), SUM(CASE WHEN AorP='A' THEN 1 ELSE 0 END) FROM MyTable GROUP BY Emp_Id更多推荐
获得缺席和存在
发布评论