Запрос SQL, сравнивающий несколько кортежей (mysql)
Недавно опубликовал этот вопрос: 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 |
+--------+--------+--------+--------+
Обратите внимание, что оценка Райана уменьшается, но затем увеличивается позже. Ответ, который я получил ранее, все еще выбирает Райана в этом случае, несмотря на то, что его оценки не всегда увеличиваются. Я понимаю, что, возможно, я не совсем ясно изложил свой исходный пост, но есть ли какой-нибудь запрос, который я мог бы сделать, чтобы это исправить?
Спасибо
РЕДАКТИРОВАТЬ: Извините, я написал это очень быстро.
По сути, мне нужен запрос, чтобы выбрать имена всех людей, которые получали более низкие баллы за каждый последующий тест, который они пытались выполнить.
то есть не принять Райана, но принять Джеймса
2 ответа
ОБНОВЛЕНИЕ: Это полная замена моего первоначального, неправильного ответа.
Проблема осложняется тем, что вы хотите одновременно выбрать два совершенно разных критерия:
- Человек имеет несколько строк в таблице
Test
, а также - Каждая строка для данного человека и даты имеет меньший балл, чем записано для того же человека на каждую более раннюю дату
Тот факт, что вы сравниваете разные строки одной и той же таблицы, предлагает решить проблему с помощью самостоятельного объединения:
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/
Я думаю, что Джон отвечает отлично, но я хочу добавить немного информации.
Используя этот базовый запрос 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