回答編集履歴

1 変更

A.Ichi

A.Ichi score 3939

2017/03/09 22:49  投稿

unionで1週間を作成してJOINしてみました。
```sql
SELECT * FROM (
 SELECT * FROM (  
   SELECT date_format(now(), '%Y.%m.%d') as date
   UNION ALL
   SELECT date_format(now() + interval 1 day, '%Y.%m.%d')
   UNION ALL
   SELECT date_format(now() + interval 2 day, '%Y.%m.%d')
   UNION ALL
   SELECT date_format(now() + interval 3 day, '%Y.%m.%d')
   UNION ALL
   SELECT date_format(now() + interval 4 day, '%Y.%m.%d')
   UNION ALL
   SELECT date_format(now() + interval 5 day, '%Y.%m.%d')
   UNION ALL
   SELECT date_format(now() + interval 6 day, '%Y.%m.%d')
 ) TBL1 JOIN (SELECT DISTINCT s_id FROM m_staff) TBL2
) TBL3
LEFT JOIN m_staff USING(s_id)
LEFT JOIN tr_attendance ON TBL3.date = date_format(a_clock_in_schedule_date, '%Y.%m.%d') AND TBL3.s_id=tr_attendance.s_id
ORDER BY TBL3.s_id, TBL3.date;
) TBL1
JOIN m_staff
LEFT JOIN tr_attendance ON TBL1.date = date_format(a_clock_in_schedule_date, '%Y.%m.%d') AND m_staff.s_id=tr_attendance.s_id
ORDER BY m_staff.s_id, TBL1.date;
;
```

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る