MySQL内连接查询

Question

我在Mysql查询方面还很新手。

我有两个表(tbl1, tbl2)。

我需要按照起始时间升序连接这两个表，并过滤日期和员工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

- user3040205

Answer 1

我认为你不需要使用join，而是需要使用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;

- Gordon Linoff

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;

- Thorsten Kettner

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

- Ravi Dhoriya ツ