查询上个月的假期/病假

Question

我正在使用laravel和sql srv

有些工人有时会休假或请病假，这些假期存储在“假期”表中，而休假的工人详细信息存储在“用户”表中。

所以实际上假期表属于Worker_id指向用户表的“id”的用户。

有两个表：“ users”和“ vacations”

1. " users" 有列: id,name

2. " vacations" 有列： vacation_id, worker_id, begin_date, end_date,description

description 只能有两个值1- vacation，2-iilness

我想做一个查询，vacation/iilness分别给我每个用户上个月的总天数

这样输出表将是

Name  | Type    | Total days | 

David | iilness |     2      |

David | vacation|     3      |

Sara  | iilness |     1      |

Sara  | Vacation|     5      |

我发现这个问题可能有助于回答： Retrive all rows from last month (Laravel + Eloquent)

但只是其中的一部分。

Answer 1

您可以使用JOIN、 聚合和DATEDIFF().棘手的部分是vacation表中是否有跨越多个月（并且可能在未来）的期间。要处理这个问题，您可以使用计算当前月份的第一个月和月底的子查询，然后使用它来：使用 a过滤vacation表JOIN：您要选择开始日期或结束日期属于当前月份的记录调整计算SELECT：如果开始日期在月初之前，您只想从月初开始计算天数；如果结束日期在月底之后，您只想计算到月底考虑：SELECT&nbsp; &nbsp; u.name,&nbsp; &nbsp; v.description,&nbsp; &nbsp; SUM(DATEDIFF(&nbsp; &nbsp; &nbsp; &nbsp; LEAST(v.end_date, m.end_date),&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; GREATEST(v.begin_date, m.begin_date)&nbsp; &nbsp; )) total_daysFROM&nbsp;&nbsp; &nbsp; user u&nbsp; &nbsp; INNER JOIN vacation v ON u.id = v.woker_id&nbsp; &nbsp; INNER JOIN (&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY begin_date,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; LAST_DAY(NOW()) end_date&nbsp; &nbsp; ) m&nbsp; &nbsp; &nbsp; &nbsp; ON v.begin_date BETWEEN m.begin_date AND m.end_date&nbsp; &nbsp; &nbsp; &nbsp; OR v.end_date&nbsp; &nbsp;BETWEEN m.begin_date AND m.end_dateGROUP BY u.id, u.name, v.description以上是针对 MySQL 的。SQL Server 中日期函数不同的等价物是：SELECT&nbsp; &nbsp; u.name,&nbsp; &nbsp; v.description,&nbsp; &nbsp; SUM(DATEDIFF(&nbsp; &nbsp; &nbsp; &nbsp; 'day',&nbsp; &nbsp; &nbsp; &nbsp; CASE WHEN v.begin_date < m.begin_date THEN m.begin_date ELSE v.begin_date END,&nbsp; &nbsp; &nbsp; &nbsp; CASE WHEN v.end_date > m.end_date&nbsp; &nbsp; &nbsp;THEN m.end_date&nbsp; &nbsp;ELSE v.end_date&nbsp; &nbsp;END&nbsp; &nbsp; )) total_daysFROM&nbsp;&nbsp; &nbsp; user u&nbsp; &nbsp; INNER JOIN vacation v ON u.id = v.woker_id&nbsp; &nbsp; INNER JOIN (&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) begin_date,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EOMONTH(GETDATE()) end_date&nbsp; &nbsp; ) m&nbsp; &nbsp; &nbsp; &nbsp; ON v.begin_date BETWEEN m.begin_date AND m.end_date&nbsp; &nbsp; &nbsp; &nbsp; OR v.end_date&nbsp; &nbsp;BETWEEN m.begin_date AND m.end_dateGROUP BY u.id, u.name, v.description

Answer 2

最后我找到了以下在 Laravel 上实现的方法$query = "&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp; &nbsp; u.name,&nbsp; &nbsp; v.description,&nbsp; &nbsp; SUM(DATEDIFF(&nbsp; &nbsp; &nbsp; &nbsp; day,&nbsp; &nbsp; &nbsp; &nbsp; CASE WHEN v.begin_date < m.begin_date THEN m.begin_date ELSE v.begin_date END,&nbsp; &nbsp; &nbsp; &nbsp; CASE WHEN v.end_date > m.end_date&nbsp; &nbsp; &nbsp;THEN m.end_date&nbsp; &nbsp;ELSE v.end_date&nbsp; &nbsp;END&nbsp; &nbsp; )+1) total_daysFROM&nbsp;&nbsp; &nbsp; [turtle].[dbo].[users] u&nbsp; &nbsp; INNER JOIN [turtle].[dbo].[vacations] v ON u.id = v.Worker_id&nbsp; &nbsp; INNER JOIN (&nbsp; &nbsp; &nbsp; &nbsp; SELECT&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATEADD(month, DATEDIFF(month, 0, DATEADD(month,-1,GETDATE())), 0) begin_date,&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; EOMONTH(DATEADD(month,-1,GETDATE())) end_date&nbsp; &nbsp; ) m&nbsp; &nbsp; &nbsp; &nbsp; ON v.begin_date BETWEEN m.begin_date AND m.end_date&nbsp; &nbsp; &nbsp; &nbsp; OR v.end_date&nbsp; &nbsp;BETWEEN m.begin_date AND m.end_dateGROUP BY u.id, u.name, v.descriptionORDER BY u.name, v.description&nbsp; &nbsp; &nbsp; &nbsp; ";&nbsp; &nbsp; &nbsp; &nbsp; $results = DB::select(DB::raw($query));&nbsp; &nbsp; &nbsp; &nbsp; dd($results);

Answer 3

对于 sql 服务器：尚未对其进行测试，但是像这样的东西可以完成这项工作。select name,type,sum(datediff(d,begin_date,end_date)) as Total_days&nbsp; from vacations v&nbsp; &nbsp; inner join users u&nbsp; &nbsp; on u.id = v.worker_id&nbsp; &nbsp; where begin_date> select dateadd(d,-DATEPART(DD,GETDATE()),GETDATE())&nbsp; &nbsp; group by name,type正如您所描述的，这仅适用于上个月的叶子。