回答編集履歴
3
調整
answer
CHANGED
|
@@ -62,5 +62,17 @@
|
|
|
62
62
|
```SQL
|
|
63
63
|
call add_calendar('2018-01-01','2025-12-31')
|
|
64
64
|
```
|
|
65
|
-
|
|
65
|
+
# 検索
|
|
66
|
+
```SQL
|
|
67
|
+
select distinct t1.uid,max(t1.name) as name from user as t1
|
|
66
|
-
|
|
68
|
+
inner join (
|
|
69
|
+
select * from calendar
|
|
70
|
+
where d between '2020-04-01' and '2023-03-31'
|
|
71
|
+
) as t2 on 1
|
|
72
|
+
left join class_user as t3
|
|
73
|
+
on t1.uid=t3.uid
|
|
74
|
+
and t2.d between t3.valid_start and t3.valid_end
|
|
75
|
+
or not t2.d between t1.valid_start and t1.valid_end
|
|
76
|
+
group by t1.uid ,t2.d
|
|
77
|
+
having count(id)=0
|
|
78
|
+
```
|
2
調整中
answer
CHANGED
|
@@ -63,12 +63,4 @@
|
|
|
63
63
|
call add_calendar('2018-01-01','2025-12-31')
|
|
64
64
|
```
|
|
65
65
|
|
|
66
|
-
上記設定が終了したら相関サブクエリーで検索可能です
|
|
67
|
-
|
|
66
|
+
※※※調整中※※※
|
|
68
|
-
select t2.name from class_user as t1
|
|
69
|
-
inner join user as t2 on t1.uid=t2.uid
|
|
70
|
-
where not exists (
|
|
71
|
-
select 1 from calendar where d between t1.valid_start and t1.valid_end
|
|
72
|
-
and d between '2020-04-01' and '2023-03-31'
|
|
73
|
-
)
|
|
74
|
-
```
|
1
調整
answer
CHANGED
|
@@ -31,6 +31,8 @@
|
|
|
31
31
|
ここでカレンダーテーブルを作っておきます。
|
|
32
32
|
プロシージャを使ってデータ投入しますがカレンダーは余裕を持って日付設定をしておきます
|
|
33
33
|
```SQL
|
|
34
|
+
create table calendar(d date primary key);
|
|
35
|
+
|
|
34
36
|
drop procedure if exists add_calendar;
|
|
35
37
|
delimiter //
|
|
36
38
|
create procedure add_calendar(IN from_date date,to_date date)
|