选择每天员工缺勤的计数

发布于 2024-11-03 01:53:07 字数 455 浏览 4 评论 0原文

好吧，假设我有三个表，如下所示：
员工（ID，姓名）
会话（ID、starts_at、ends_at、day）
缺勤（ID、employeeID、sessionID）

可以说每天有多个会话。
是否可以选择计算会话表上每个不同日期的会话中员工的缺勤情况并将其分组如下：（

--------------------------------
| Name |2011-01-15| 2011-01-16 | 
|      |          |            |
|john  |  2       |   0        |
|dave  |  4       |    1       |

列数（天数）取决于会话表上的天数，假设我只想要那些上周或本周
谢谢。

原文

alright, so lets say i have three tables, as follows :
employees(ID, name)
sessions(ID, starts_at, ends_at, day)
absences(ID, employeeID, sessionID)

lets say there are multiple sessions per day.
is it possible to select count the Absences of employees from sessions for each distinct day on the sessions table and group them as follows :

--------------------------------
| Name |2011-01-15| 2011-01-16 | 
|      |          |            |
|john  |  2       |   0        |
|dave  |  4       |    1       |

(number of columns (days) depends on number of days on the sessions table, lets say i only want those of last or current week
thank you.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

与风相奔跑 2024-11-10 01:53:07

您应该使用 PIVOT 语句。就这样：

SELECT Name, [2011-01-15], [2011-01-16]
FROM    
(SELECT  [Day],Name
    FROM [Absences] A left join
         [Sessions] S on A.sessionID = S.ID
    GROUP BY [Day],Name) AS SourceTable
PIVOT
(
COUNT([Day])
FOR [Day] IN ([2011-01-15], [2011-01-16])
) AS PivotTable;

You should use the PIVOT statement. There it goes:

SELECT Name, [2011-01-15], [2011-01-16]
FROM    
(SELECT  [Day],Name
    FROM [Absences] A left join
         [Sessions] S on A.sessionID = S.ID
    GROUP BY [Day],Name) AS SourceTable
PIVOT
(
COUNT([Day])
FOR [Day] IN ([2011-01-15], [2011-01-16])
) AS PivotTable;

回复收藏 0 原文