我有三个表:
第一个月
id date days
1 2015-06-01 Monday
2 2015-06-02 Tuesday
3 2015-06-03 Wednesday
4 2015-06-04 Thursday
5 2015-06-05 Friday
6 2015-06-06 Saturday
7 2015-06-07 Sunday
第二名
id user_name date checkin
1 ali 2015-06-01 10:43:17
2 ali 2015-06-03 10:22:39
3 ali 2015-06-04 13:36:52
4 ali 2015-06-05 14:36:52
第三位是出勤
id user_name date checkout
1 ali 2015-06-01 05:03:17
2 ali 2015-06-03 06:00:39
3 ali 2015-06-04 06:36:02
4 ali 2015-06-05 06:06:02
，我想要这样的结果:
username date checkin checkout day
ali 2015-06-01 10:43:17 05:03:17 Monday
2015-06-02 Tuesday
ali 2015-06-03 10:22:39 06:00:39 Wednesday
ali 2015-06-04 13:36:52 06:36:02 Thursday
ali 2015-06-05 14:36:52 06:06:02 Friday
2015-06-05 Saturday
2015-06-05 Sunday
如何显示这样的结果?
我正在使用这个查询:
SELECT *
FROM attend
WHERE user_name='ali'
AND date BETWEEN '2015-06-01' AND '2015-06-07'
Order by date
像这样
select t3.user_name, t3.date, t2.checkin, t3.checkout, t1.day
from 3rd_table as t3
inner join 2nd_table as t2 on t2.date = t3.date and t2.user_name = t3.user_name
inner join 1st_table as t1 on t1.date = t3.date;
像这样使用…
select * from month as m
left join attend as a where m.date = a.date
left join attendout as o where m.date = o.date
where a.user_name = 'ali'
and ... // conditions you want on..
SELECT * FROM month
INNER JOIN attend
ON month.id = attend.id
INNER JOIN attendout
ON attend.id = attendout.id;