MySQL:查找值不断增加的ID

Question

我有下表:

create table my_table
(
 SubjectID int,
 Date Date,
 Test_Value int
);

insert into my_table(SubjectID, Date, Test_Value)
values
 (1, '2014-01-01', 55),
 (1, '2014-01-05', 170),
 (1, '2014-01-30', 160),
 (2, '2014-01-02', 175),
 (2, '2014-01-20', 166),
 (2, '2014-01-21', 160),
 (3, '2014-01-05', 70),
 (3, '2014-01-07', 75),
 (3, '2014-01-11', 180)

我想找到 Test_Value 随着时间不断增加的 ID。在此示例中，只有 SubjectID 3 满足该条件。你能写出代码来找出这个吗？感谢您一如既往的帮助。

Answer 1

SELECT *
FROM my_table o
WHERE NOT EXISTS (
    SELECT null
    FROM my_table t1
    INNER JOIN my_table t2 ON t2.Date > t1.Date AND t2.Test_Value < t1.Test_Value AND t1.SubjectID = t2.SubjectID
    WHERE t1.SubjectID = o.SubjectID
)

内部查询将选择所有违反要求的实体:它们的日期较晚且值最小。然后外部选择与内部查询不匹配的实体。

SQLFiddle:http://www.sqlfiddle.com/#!2/1a7ba/12

PS:大概如果你只需要一个 id - 使用 SELECT DISTINCT SubjectID