Mysql内连接查询

Question

我是Mysql查询的新手。

我有两个表(tbl1，tbl2)

我需要用start_time升序连接这两个表，并过滤date和emp_id

此处的表字段不同(start-time -> in-time、end-time -> out-time)

tbl-1:

id      start-time      end-time        date            emp_id

1       09:00:00        09:00:59        2014-05-14        1

2       10:00:00        10:00:59        2014-05-14        1

3       12:00:00        12:00:59        2014-05-14        1

4       14:00:00        14:00:59        2014-05-14        1

5       16:00:00        17:00:59        2014-05-13        1


tbl-2:

id      in-time         out-time        date            emp_id

1       11:00:00        11:00:59        2014-05-14        1

2       13:00:00        13:00:59        2014-05-14        1

3       15:00:00        15:00:59        2014-05-14        1

4       18:00:00        19:00:59        2014-05-14        2

5       20:00:00        20:00:59        2014-05-15        1


filterd by date, emp_id ordered by date

result-tbl:

id      start-time      end-time        date            emp_id

1       09:00:00        09:00:59        2014-05-14        1

2       10:00:00        10:00:59        2014-05-14        1

3       11:00:00        11:00:59        2014-05-14        1

4       12:00:00        12:00:59        2014-05-14        1

5       13:00:00        13:00:59        2014-05-14        1

6       14:00:00        14:00:59        2014-05-14        1

7       15:00:00        15:00:59        2014-05-14        1

Answer 1

我不认为你想要加入。我认为您需要一个union all以及一个重新分配id的方法。类似于：

select (@rn := @rn + 1) as id, intime as starttime, outtime as outtime, emp_id
from ((select * from tbl1)
      union all
      (select * from tbl2)
     ) t cross join
     (select @rn := 0) var
where emp_id = 1 and
      intime <= '18:00:00'
order by intime;

Answer 2

你想有一个记录的联盟吗？

select in_time, out_time, date, emp_id from tbl1
union all
select star_time, end_time, date, emp_id from tbl2
order by in_time, out_time, date, emp_id;

编辑:加过滤器：

select in_time, out_time, date, emp_id from tbl1
where emp_id = 1 and date = '2014-05-14'
union all
select star_time, end_time, date, emp_id from tbl2
where emp_id = 1 and date = '2014-05-14'
order by in_time, out_time;

Answer 3

这可能会解决你的问题。

select *
from (
        select id,star_time,end_time,date,emp_id
        from tbl1
        where [date-filter-condition]
        union all
        select id,in_time as star_time,out_time end_time,date,emp_id
        from tbl2
        where [date-filter-condition]
) tmp
where tmp.date==[some-date-filter] and tmp.emp_id=[some-emp-id-filter]
order by tmp.date