我正在使用laravel和sql srv有些工人有時(shí)會(huì)休假或請(qǐng)病假，這些假期存儲(chǔ)在“假期”表中，而休假的工人詳細(xì)信息存儲(chǔ)在“用戶”表中。所以實(shí)際上假期表屬于Worker_id指向用戶表的“id”的用戶。有兩個(gè)表：“ users”和“ vacations”" users" 有列: id,name" vacations" 有列： vacation_id, worker_id, begin_date, end_date,descriptiondescription 只能有兩個(gè)值1- vacation，2-iilness我想做一個(gè)查詢，vacation/iilness分別給我每個(gè)用戶上個(gè)月的總天數(shù)這樣輸出表將是Name | Type | Total days | David | iilness | 2 |David | vacation| 3 |Sara | iilness | 1 |Sara | Vacation| 5 |我發(fā)現(xiàn)這個(gè)問(wèn)題可能有助于回答： Retrive all rows from last month (Laravel + Eloquent)但只是其中的一部分。
3 回答
元芳怎么了
TA貢獻(xiàn)1798條經(jīng)驗(yàn) 獲得超7個(gè)贊
您可以使用JOIN、 聚合和DATEDIFF().
棘手的部分是vacation表中是否有跨越多個(gè)月（并且可能在未來(lái)）的期間。要處理這個(gè)問(wèn)題，您可以使用計(jì)算當(dāng)前月份的第一個(gè)月和月底的子查詢，然后使用它來(lái)：
使用 a過(guò)濾vacation表JOIN：您要選擇開(kāi)始日期或結(jié)束日期屬于當(dāng)前月份的記錄
調(diào)整計(jì)算SELECT：如果開(kāi)始日期在月初之前，您只想從月初開(kāi)始計(jì)算天數(shù)；如果結(jié)束日期在月底之后，您只想計(jì)算到月底
考慮：
SELECT
u.name,
v.description,
SUM(DATEDIFF(
LEAST(v.end_date, m.end_date),
GREATEST(v.begin_date, m.begin_date)
)) total_days
FROM
user u
INNER JOIN vacation v ON u.id = v.woker_id
INNER JOIN (
SELECT
LAST_DAY(NOW() - INTERVAL 1 MONTH) + INTERVAL 1 DAY begin_date,
LAST_DAY(NOW()) end_date
) m
ON v.begin_date BETWEEN m.begin_date AND m.end_date
OR v.end_date BETWEEN m.begin_date AND m.end_date
GROUP BY u.id, u.name, v.description
以上是針對(duì) MySQL 的。SQL Server 中日期函數(shù)不同的等價(jià)物是：
SELECT
u.name,
v.description,
SUM(DATEDIFF(
'day',
CASE WHEN v.begin_date < m.begin_date THEN m.begin_date ELSE v.begin_date END,
CASE WHEN v.end_date > m.end_date THEN m.end_date ELSE v.end_date END
)) total_days
FROM
user u
INNER JOIN vacation v ON u.id = v.woker_id
INNER JOIN (
SELECT
DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) begin_date,
EOMONTH(GETDATE()) end_date
) m
ON v.begin_date BETWEEN m.begin_date AND m.end_date
OR v.end_date BETWEEN m.begin_date AND m.end_date
GROUP BY u.id, u.name, v.description
RISEBY
TA貢獻(xiàn)1856條經(jīng)驗(yàn) 獲得超5個(gè)贊
最后我找到了以下在 Laravel 上實(shí)現(xiàn)的方法
$query = "
SELECT
u.name,
v.description,
SUM(DATEDIFF(
day,
CASE WHEN v.begin_date < m.begin_date THEN m.begin_date ELSE v.begin_date END,
CASE WHEN v.end_date > m.end_date THEN m.end_date ELSE v.end_date END
)+1) total_days
FROM
[turtle].[dbo].[users] u
INNER JOIN [turtle].[dbo].[vacations] v ON u.id = v.Worker_id
INNER JOIN (
SELECT
DATEADD(month, DATEDIFF(month, 0, DATEADD(month,-1,GETDATE())), 0) begin_date,
EOMONTH(DATEADD(month,-1,GETDATE())) end_date
) m
ON v.begin_date BETWEEN m.begin_date AND m.end_date
OR v.end_date BETWEEN m.begin_date AND m.end_date
GROUP BY u.id, u.name, v.description
ORDER BY u.name, v.description
";
$results = DB::select(DB::raw($query));
dd($results);
婷婷同學(xué)_
TA貢獻(xiàn)1844條經(jīng)驗(yàn) 獲得超8個(gè)贊
對(duì)于 sql 服務(wù)器：
尚未對(duì)其進(jìn)行測(cè)試，但是像這樣的東西可以完成這項(xiàng)工作。
select name,type,sum(datediff(d,begin_date,end_date)) as Total_days from vacations v
inner join users u
on u.id = v.worker_id
where begin_date> select dateadd(d,-DATEPART(DD,GETDATE()),GETDATE())
group by name,type
正如您所描述的，這僅適用于上個(gè)月的葉子。
- 3 回答
- 0 關(guān)注
- 179 瀏覽
添加回答
舉報(bào)
0/150
提交
取消