MySQL 获取结果的最大 ID，除非列中的值等于 1

Question

我知道如何获取 Max ID。这很容易。但我遇到的情况是 MaxID 可能是值为 1 的行。示例.. 我有下表:

    CREATE TEMPORARY TABLE people (
    id INT NOT NULL
    ,NAME VARCHAR(50) NOT NULL
);

INSERT INTO people (id, NAME) VALUES (1, 'tony');
INSERT INTO people (id, NAME) VALUES (2, 'dave');
INSERT INTO people (id, NAME) VALUES (3, 'dan');

CREATE TEMPORARY TABLE orders (
    id INT NOT NULL
    ,peopleid INT NOT NULL
    ,VALUE INT
);

INSERT INTO orders (id, peopleid, VALUE) VALUES (1, 1, NULL);
INSERT INTO orders (id, peopleid, VALUE) VALUES (2, 1, 1);
INSERT INTO orders (id, peopleid, VALUE) VALUES (3, 1, NULL);
INSERT INTO orders (id, peopleid, VALUE) VALUES (4, 2, NULL);
INSERT INTO orders (id, peopleid, VALUE) VALUES (5, 2, NULL);
INSERT INTO orders (id, peopleid, VALUE) VALUES (6, 2, NULL);
INSERT INTO orders (id, peopleid, VALUE) VALUES (7, 2, NULL);
INSERT INTO orders (id, peopleid, VALUE) VALUES (8, 1, NULL);
INSERT INTO orders (id, peopleid, VALUE) VALUES (9, 2, NULL);

当我运行查询时:

SELECT * FROM people AS p
    LEFT JOIN orders AS o ON o.peopleid = p.id

我得到结果:

id  name  id  peopleid  value
1   tony  1   1         null
1   tony  2   1         1
1   tony  3   1         null
1   tony  8   1         null
2   dave  4   2         null
2   dave  5   2         null
2   dave  6   2         null
2   dave  7   2         null
2   dave  9   2         null
3   dan   null null     null 

我需要结果

id  name  id  peopleid  value
1   tony  2   1         1
2   dave  9   2         null
3   dan   null null     null 

Answer 1

 SELECT id FROM tablename
 ORDER BY value=1 DESC, id DESC
 LIMIT 1;


 SELECT persons.personid,persons.name,othertable.id
 FROM persons
 LEFT JOIN othertable 
    ON othertable.personid = persons.personid
 LEFT JOIN othertable check
    ON check.personid = persons.personid
    ON  check.id > othertable.id
    AND (othertable.value!=1 OR check.value=1)
 WHERE check.personid IS NULL;

最后:

SELECT people.id,people.NAME,orders.id,orders.VALUE
FROM people
LEFT JOIN orders 
  ON orders.peopleid = people.id
LEFT JOIN orders checkbigger
  ON  checkbigger.peopleid = people.id
  AND (
    (
      checkbigger.id > orders.id
      AND orders.value <=> checkbigger.value
    )
    OR
    (
      orders.value IS NULL AND checkbigger.value IS NOT NULL
    )
  )
WHERE checkbigger.id IS NULL;