別のテーブルの2つの日付フィールドに基づいて1つのテーブルのレコード数を取得する方法
例:
表1:葉
| leav_id | from | to |
| 1 | 2015-10-20 | 2015-10-27 |
| 2 | 2015-11-10 | 2015-10-12 |
表2:休日
| holi_id | On | Name |
| 1 | 2015-12-25 | Christmas |
取得したいnumber days between from and to in table leaves excluding the count of holidays from table holiday in that range
。
これがあなたができる方法です、私は計算をするためにいくつかの休日を作成しました
mysql> select * from leaves ;
+---------+------------+------------+
| leav_id | from | to |
+---------+------------+------------+
| 1 | 2015-10-20 | 2015-10-27 |
| 2 | 2015-12-20 | 2015-12-30 |
+---------+------------+------------+
2 rows in set (0.00 sec)
mysql> select * from holidays ;
+---------+------------+----------+
| holi_id | on | name |
+---------+------------+----------+
| 1 | 2015-12-25 | X-Mass |
| 2 | 2015-12-26 | Saturday |
| 3 | 2015-12-26 | Sunday |
| 4 | 2015-10-24 | Saturday |
| 5 | 2015-10-25 | Sunday |
+---------+------------+----------+
したがって、次のクエリは、休日を除いた葉の総数を示します。
select
t1.leav_id,
(
datediff(t1.`to`,t1.`from`)
-
sum(
case when t2.`on` >= t1.`from` and t2.`on` <= t1.`to`
then 1 else 0 end
)
) as total_leave
from leaves t1
left join holidays t2 on t2.`on` >= t1.`from`
and t2.`on` <= t1.`to`
group by t1.leav_id
結果は次のようになります
+---------+-------------+
| leav_id | total_leave |
+---------+-------------+
| 1 | 5 |
| 2 | 7 |
+---------+-------------+
また、datediff
関数によって正しい結果が得られない場合があることにも注意してください。
mysql> select datediff('2015-12-30','2015-12-20') as d ;
+------+
| d |
+------+
| 10 |
+------+
ここではその10日後、しかし、休暇からその11すなわち計算されたとき2015-12-20
まで2015-12-30
したがって、上記のクエリをリファクタリングして、+1
として追加する必要があります
select
t1.leav_id,
(
datediff(t1.`to`,t1.`from`)
-
sum(
case when t2.`on` >= t1.`from` and t2.`on` <= t1.`to`
then 1 else 0 end
)
)+1 as total_leave
from leaves t1
left join holidays t2 on t2.`on` >= t1.`from`
and t2.`on` <= t1.`to`
group by t1.leav_id
そしてとして与える
+---------+-------------+
| leav_id | total_leave |
+---------+-------------+
| 1 | 6 |
| 2 | 8 |
+---------+-------------+
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加