限制表连接中的查询结果

Question

I need select the employee id's from the last delivery unless the status = OTD from the hta table. Then I need to get all remaining employee id's from the employee table and display those results. Unfortunately employees with a status of otd are also in the employee table. Can I write a query that will return these results? Or do I have to do it differently? If so can you guide me.

   SELECT h.employee_id
        , MAX(h.delivery_date)
        , e.employee_first
        , h.status 
     FROM TABLE_HTA h 
    RIGHT
     JOIN TABLE_EMPLOYEES e 
       ON h.employee_id = e.employee_id 
    WHERE h.status <> "OTD" 
    GROUP 
       BY h.employee_id 
    ORDER 
       BY MAX(h.delivery_date) ASC

Table_hta<br>
order_id |  employee_id | status | delivery_date |<br>
       1 |    23        |  OTD   | 1/1/15 3:45pm<br>
       2 |     24       |DELIVER | 1/1/15 3:50pm<br>
       3 |     25       |DELIVER | 1/1/15 3:51pm<br>
<br>
Table_employees<br>
employee_id | employee_first |<br>
    23        Bob<br>
    24        James<br>
    25        Henry<br>
    26        Sally<br>

Results<br>
employee_id |  employee_first | delivery_date<br>
       26 |Sally<br>
       24 |James |1/1/15 3:50pm<br>
       25 |James |1/1/15 3:51pm<br>

Sally was included because she did not have the status OTD. Bob was excluded because he had the status OTD.

Answer 1

You'll get more and better answers if you include CREATE TABLE and INSERT statements in your question.

create table table_hta (
  order_id integer primary key,
  employee_id integer not null,
  status varchar(10) not null,
  delivery_date datetime not null
);

insert into table_hta values
(1, 23, 'OTD', '2015-01-01 3:45'),
(2, 24, 'DELIVER', '2015-01-01 3:50'),
(3, 25, 'DELIVER', '2015-01-01 3:51');

create table table_employees (
  employee_id integer primary key,
  employee_first varchar(10) not null
);

insert into table_employees values
(23, 'Bob'),
(24, 'James'),
(25, 'Henry'),
(26, 'Sally');

The employees you're interested in are

• all the employees,
• except the ones that have a status of 'OTD'.

All the employees are in "table_employees". The exceptions are in "table_hta". This will give you the employees you're interested in.

select employee_id 
from table_employees
where employee_id not in (select employee_id
                          from table_hta
                          where status = 'OTD');

We can use the WHERE clause just as it is. To get the max delivery dates (a poor name for delivery times, by the way), we do a left join to preserve the employee id numbers we're interested in.

select e.employee_id, e.employee_first, h.delivery_date
from table_employees e
left join (select employee_id, max(delivery_date) as delivery_date 
           from table_hta group by employee_id) h 
       on e.employee_id = h.employee_id
where e.employee_id not in (select employee_id 
                            from table_hta 
                            where status = 'OTD');

---

The fact that you're interested in the maximum delivery date (delivery time) suggests that the table allows multiple delivery dates. If it allows multiple delivery dates, it probably allows data like this, too.

order_id |  employee_id | status | delivery_date |
       1 |     24       |  OTD   | 1/1/15 3:45pm
       2 |     24       |DELIVER | 1/1/15 3:50pm

Think hard about what you want to include in situations like this.