mysql 同一表记录中的2行比较

Question

我在网上到处搜索，但没有找到解决我的问题的方法。我有一个名为 master 的表，其中保存有问题的设备的记录，当设备首次记录在数据库中时，作业状态为 cat_id = 11，当有人修复它时，他们再次输入相同的信息并将状态更改为 cat_id = 1 或 2 或 3 等等。当cat_id(状态)更改为cat_id = 11以外的其他内容时，我希望带有“mo_number”和cat_id =其他内容的作业不要显示在选择查询中。

表:主表

id   mo_number   part_number   cat_id       date_created   user_id
15    7200023         347893        11      01/03/2016     1
33    7200023         347893        1       10/03/2016     5
34    7200045         457890        11      11/03/2016     1

expected :
id   mo_number   part_number   cat_id       date_created   user_id
34    7200045         457890        11      11/03/2016     1

这是我完整的选择查询:

SELECT m.comment_id, m.mo_number, m.part_number, m.serial_number, m.comment,
       m.date_created, CONCAT(u.first_name, ' ', u.last_name) as name
       FROM master AS m
         INNER JOIN users as u ON u.user_id = m.user_id
          INNER JOIN category as c ON m.cat_id = c.cat_id
       WHERE u.user_id = '1' AND m.cat_id = '11'
    ORDER BY m.mo_number DESC

我不知道如何编写查询，以便该作业不会在表中显示为仍处于打开状态。如果有人可以提供帮助，我将不胜感激。

Answer 1

SQL Fiddle

MySQL 5.5 架构设置:

create table users (user_id int, first_name varchar(100), last_name nvarchar(100));
insert users (user_id, first_name, last_name)
select 1, 'John', 'Smith'
union
select 5, 'Neo', 'Anderson'
;

create table category (cat_id int, name varchar(100));
insert category (cat_id, name)
select 1, 'closed'
union
select 2, 'not so closed but not open too :)'
union
select 11, 'open'
;

create table master (
  id int, mo_number varchar(10), part_number varchar(10), serial_number varchar(10),
  cat_id int,  date_created date, user_id int, comment varchar(100));

insert master (id, mo_number, part_number, serial_number, cat_id, date_created, user_id, comment)
select 15, '7200023', '347893', 'SN00001', 11, '2016-03-01', 1, 'seems we can do it'
union
select 33, '7200023', '347893', 'SN00001', 1, '2016-03-10', 5, 'tested by Morpheus'
union
select 34, '7200045', '457890', 'SN00002', 11, '2016-03-11', 1, 'again that Miss drop it to bath '

查询 1:

SELECT m.mo_number, m.part_number, m.serial_number, m.comment,
  m.date_created, CONCAT(u.first_name, ' ', u.last_name) as name
FROM master AS m
  INNER JOIN users as u ON u.user_id = m.user_id
  INNER JOIN category as c ON m.cat_id = c.cat_id
WHERE m.cat_id = 11 and not exists(
  select 1 from master as m1 
  where m1.cat_id <> 11 
    and m1.date_created > m.date_created 
    and m1.mo_number = m.mo_number)
ORDER BY m.mo_number DESC

<强> Results :

| mo_number | part_number | serial_number |                          comment |            date_created |       name |
|-----------|-------------|---------------|----------------------------------|-------------------------|------------|
|   7200045 |      457890 |       SN00002 | again that Miss drop it to bath  | March, 11 2016 00:00:00 | John Smith |