Запрос SQL, сравнивающий несколько кортежей (mysql)

Question

Недавно опубликовал этот вопрос: SQL-запрос, сравнивающий атрибут в нескольких кортежах на основе значений другого атрибута в отношении

Таблица ниже такая же, но с небольшой модификацией:

                    Test

    +--------+--------+--------+--------+
    |  Name  |  Date  |Location| Score  |
    +--------+--------+--------+--------+
    | Steven |03-05-12| 120000 |   78   |
    +--------+--------+--------+--------+
    | James  |04-09-11| 110000 |   67   |
    +--------+--------+--------+--------+
    | James  |06-22-11| 110000 |   58   |
    +--------+--------+--------+--------+
    |  Ryan  |10-11-13| 250000 |   62   |
    +--------+--------+--------+--------+
    |  Ryan  |12-19-13| 180000 |   55   |
    +--------+--------+--------+--------+
    |  Ryan  |01-20-15| 180000 |   99   |
    +--------+--------+--------+--------+

Обратите внимание, что оценка Райана уменьшается, но затем увеличивается позже. Ответ, который я получил ранее, все еще выбирает Райана в этом случае, несмотря на то, что его оценки не всегда увеличиваются. Я понимаю, что, возможно, я не совсем ясно изложил свой исходный пост, но есть ли какой-нибудь запрос, который я мог бы сделать, чтобы это исправить?

Спасибо

РЕДАКТИРОВАТЬ: Извините, я написал это очень быстро.

По сути, мне нужен запрос, чтобы выбрать имена всех людей, которые получали более низкие баллы за каждый последующий тест, который они пытались выполнить.

то есть не принять Райана, но принять Джеймса

Answer 1

ОБНОВЛЕНИЕ: Это полная замена моего первоначального, неправильного ответа.

Проблема осложняется тем, что вы хотите одновременно выбрать два совершенно разных критерия:

1. Человек имеет несколько строк в таблице Test, а также
2. Каждая строка для данного человека и даты имеет меньший балл, чем записано для того же человека на каждую более раннюю дату

Тот факт, что вы сравниваете разные строки одной и той же таблицы, предлагает решить проблему с помощью самостоятельного объединения:

FROM
  Test t1
  join Test t2
    on t1.Name = t2.Name

Если мы отфильтруем результаты, возникающие при соединении каждой строки с самим собой, то останутся только строки, относящиеся к людям, на которые ссылаются несколько строк. Причем для рядов R1 и R2 оба с одинаковыми Nameнам нужно рассмотреть только одну из пар (R1, R2) и (R2, R1). Мы можем решить обе эти проблемы с помощью одного фильтра:

WHERE t1.Date < t2.Date

Мы хотим выполнить анализ объединенного результата на Name-от-Name основа; это предполагает агрегированный запрос (если доступны подходящие агрегатные функции):

GROUP BY t1.Name

Нам нужны только те агрегаты, которые удовлетворяют нашим критериям, и эти критерии состоят в том, чтобы каждая строка, прошедшая WHERE фильтр, и, следовательно, имеет t1.Date < t2.Date, также имеет t1.Score > t2.Score, Здесь мы можем положиться на тот факт, что реляционные операторы вычисляются в число: 1 по правде говоря, и 0 за ложь. Если мы добавим эти значения в каждую группу, мы сможем определить, удовлетворяет ли каждая строка критерию:

HAVING SUM(t1.Score > t2.Score) = COUNT(*)

Учитывая, что мы хотим выбрать только имена (которые уже удобно различаются, любезно предоставлены группировкой), все вместе

SELECT t1.Name
FROM
  Test t1
  join Test t2
    on t1.Name = t2.Name
WHERE t1.Date < t2.Date
GROUP BY t1.Name
HAVING SUM(t1.Score > t2.Score) = COUNT(*)

Вот скрипка с примерами данных, взятыми из вопроса: http://sqlfiddle.com/

Answer 2

Я думаю, что Джон отвечает отлично, но я хочу добавить немного информации.

Используя этот базовый запрос SqlFiddleDemo, вы можете включить все условия в left join

select t1.Name, t1.Date, t1.Score, t2.Date, t2.Score
from
  student t1
  left join student t2 
         on t1.Name = t2.Name
        and t1.Date < t2.Date
        and t1.Score <= t2.Score

|   Name |                       Date | Score |                      Date |  Score |
|--------|----------------------------|-------|---------------------------|--------|
|   Ryan |  October, 11 2013 00:00:00 |    62 | January, 20 2015 00:00:00 |     99 |
|   Ryan | December, 19 2013 00:00:00 |    55 | January, 20 2015 00:00:00 |     99 |
| Steven |    March, 05 2012 00:00:00 |    78 |                    (null) | (null) |
|  James |    April, 09 2011 00:00:00 |    67 |                    (null) | (null) |
|  James |     June, 22 2011 00:00:00 |    58 |                    (null) | (null) |
|   Ryan |  January, 20 2015 00:00:00 |    99 |                    (null) | (null) |

Тогда вы можете использовать условный SUM чтобы узнать, во сколько раз каждое имя увеличивает его счет. В этом случае Ryan будет 2

select t1.Name, SUM(IF(t2.Date IS NULL, 0, 1)) as increase_score
from
  student t1
  left join student t2 
         on t1.Name = t2.Name
        and t1.Date < t2.Date
        and t1.Score <= t2.Score
GROUP BY t1.Name
HAVING 
    increase_score = 0                    -- not increase score in any test
and count(*) > 1                          -- present more than one test