У меня две таблицы, одна таблица называется users ,
fsname emailaddress
и вторая таблица называется attempts с emailaddress , score и datetime .
Теперь то, что я хотел сделать, это сначала заказать таблицу attempts по datetime а затем выбрать, затем присоединиться к таблице attempt таблицей users если они имеют один emailaddress а затем выбрать окончательные попытки каждого уникального пользователя.
Короче говоря, я должен выбрать последнюю попытку каждого пользователя, присоединившись к этой таблице, и это запрос, который я создал для этого,
$query = "SELECT distinct users.fsname, users.emailaddress, attempts.score FROM users INNER JOIN attempts ON users.emailaddress = attempts.emailaddress ORDER BY datetime DESC";
Этот запрос сначала предполагает упорядочить attempts datetime и выбирать только значения с отличным именем, которое здесь является fsname .
но когда я выполняю вышеуказанный запрос, он возвращает результат с нестандартными значениями fsname хотя я использую DISTINCT с fsname .
Может кто-нибудь, пожалуйста, скажите мне, почему DISTINCT не работает, чтобы выбрать только отдельное имя fsname ?
Я пробовал оба DISTINCT fsname и DISTINCT(fsname) но ни один из них не работает.
Это не работает, как вы думаете, и в документации объясняется значение DISTINCT : речь идет о разных строках :
Опции
ALLиDISTINCTопределяют, должны ли возвращаться повторяющиеся строки.ALL(по умолчанию) указывает, что все соответствующие строки должны быть возвращены, включая дубликаты.DISTINCTуказывает удаление повторяющихся строк из набора результатов. Ошибка указать оба параметра.DISTINCTROW– синонимDISTINCT.
(источник: http://dev.mysql.com/doc/refman/5.7/en/select.html )
Вам нужно сгруппировать строки пользователем, чтобы получить одну строку для каждого пользователя, но, к сожалению, вы не можете получить их самую последнюю оценку таким образом. Вы можете получить максимальный, минимальный, средний балл и другие вычисленные значения. Проверьте список агрегатных функций GROUP BY .
Это запрос, который получает нужные значения:
SELECT u.fsname, u.emailaddress, la.score FROM users u INNER JOIN attempts la # 'la' from 'last attempt' ON u.emailaddress = la.emailaddress LEFT JOIN attempts mr # 'mr' from 'more recent' (than last attempt) ON la.emailaddress = mr.emailaddress AND la.datetime < mr.datetime WHERE mr.datetime IS NULL
Он объединяет users таблиц (с псевдонимом u ) с attempts таблицы (с псевдонимом as la , short для «последней попытки»), используя emailaddress в качестве столбца соответствия. Это соединение, которое у вас уже есть в вашем запросе, я добавил псевдонимы, потому что они помогают вам писать меньше с этого момента.
Затем он снова присоединяется к таблице attempts (псевдоним как mr из « более поздней, чем последняя попытка»). Он соответствует каждой попытке из la со всеми попытками mr одного и того же пользователя (идентифицируется их emailaddress ) и имеет более недавнее datetime . LEFT JOIN гарантирует, что каждая строка из la совпадает по крайней мере с одной строкой от mr . Строки из la которые не имеют соответствия в mr представляют собой строки, которые имеют самые большие значения datetime для каждого emailaddress . Они сопоставляются строками, заполненными NULL (для части mr ).
Наконец, WHERE хранит только строки с NULL в столбце datetime строки, выбранной из mr . Это строки, которые соответствуют последним записям из la для каждого значения emailaddress .
Для быстрого выполнения этого запроса ( любого запроса! ) Нужны индексы для столбцов, используемых в предложениях JOIN , WHERE , GROUP BY и ORDER BY .
Вы не должны использовать emailaddress в таблице, чтобы идентифицировать пользователя. Вы должны иметь PK (первичный ключ) для users таблицы и использовать это как FK (внешний ключ) в attempts таблицы (и другие таблицы, которые относятся к пользователю). Если emailaddress является PK users таблицы, измените его на UNIQUE INDEX и вместо этого используйте новый INTEGER AUTO INCREMENT ed column userId вместо PK . Индексы на числовых столбцах быстрее и используют меньше места, чем индексы столбцов строки.