Присоединиться, но получить только один раз с новейшей датой

#mysql

Вопрос:

у меня есть такие данные.
Таблица customer

 | customerId | fullName     | sex
| 1          | Budi Arsana  | Male
| 2          | Peter Parker | Male

и другое имя таблицы transaction

 | Id | bookingDate | customer
| 1  | 2011-09-01  | 1
| 2  | 2011-09-30  | 1
| 3  | 2011-09-01  | 2

И я хочу получить результат с объединением этих 2 таблиц, которые выбирают клиента, у которого есть транзакция, а затем получают только 1 транзакцию с последней датой. Вот так

 | customerId | fullName     | sex  | transId | bookingDate
| 1          | Budi Arsana  | Male | 2       | 2011-09-30
| 2          | Peter Parker | Male | 3       | 2011-09-01

Ответ №1:

 SELECT c.customerId, c.fullName, c.sex, t.Id AS transId, t.bookingDate
    FROM customer c
        INNER JOIN (SELECT customer, MAX(bookingDate) AS MaxDate
                        FROM transaction
                        GROUP BY customer) q
            ON c.customerId = q.customer
        INNER JOIN transaction t
            ON q.customer = t.customer
                 AND q.MaxDate = t.bookingDate;

1. могу ли я узнать, что означает 2-я строка? FROM customer c , Вы имели в виду FROM customer AS c ?

2. @GusDeCooL AS Необязательный, поэтому обе версии означают одно и то же.

3. Да, это короткая форма, AS можно опустить. Я бы не рекомендовал это, по крайней мере, когда он используется для псевдонимов столбцов. Легко пропустить ввод запятой, но не так легко обнаружить, что вы пропустили ее при отладке. Т.е. SELECT id group, name FROM users может быть сложно найти. Для ввода и AS требуется всего несколько нажатий клавиш, но ИМХО это намного облегчает чтение

4. @Nikoloff Как вы можете видеть в моем ответе, я придерживаюсь смешанного подхода. Я использую столбцы AS псевдонимов when по указанным вами причинам, но обычно опускаю их после имен таблиц, поскольку в этом случае связь более очевидна.

5. @Joe Stefanelli, согласен, поэтому я уточнил, что я говорю о столбцах, а не о таблицах 🙂

Ответ №2:

Использование объединения с самоисключением:

 SELECT c.customerId, c.fullName, c.sex, t.Id AS transId, t.bookingDate
  FROM customer c
       LEFT JOIN transaction t ON t.customer = c.customerId
       LEFT JOIN transaction t2 ON t2.customer = t.customer
   AND t2.bookingDate > t.bookingDate 
 WHERE t2.Id IS NULL;

Принцип заключается в том, что вы создаете другое соединение с рабочей копией transaction таблицы ( t2 ) и используете его для удаления из конечного результирующего набора всех, кроме самой последней записи в transaction ( t ) .

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

1. 1 прекрасно работает, если вы удалите лишнее t transaction t t2 в своем втором LEFT JOIN : sqlize.com/MpaCjlZb67

2. Ах да, спасибо @mella. Отредактировано. И спасибо за совет о sqlize. Раньше такого не видел.