MySQL left join с вложенным запросом выдает разные результаты с разной сортировкой

#mysql #join #group-by #left-join

#mysql #Присоединиться #группировка по #левое соединение

Вопрос:

У меня есть 2 таблицы: счета-фактуры и платежные транзакции. где таблица paymentTransactions содержит записи платежных операций счетов-фактур — может иметь несколько записей.

Таблица: счета-фактуры:

 invoiceId, customerId, amount

Таблица: Платежные транзакции:

 paymentTransactionId, invoiceId, status

Требование состоит в том, чтобы получить счета-фактуры вместе с последним журналом платежных транзакций.

Я попробовал приведенный ниже запрос, но он возвращает разные результаты с разной сортировкой.

 SELECT i.invoiceId,recentTrans.maxTransId,  cts.status as recentStatus  FROM invoices i LEFT JOIN  (SELECT MAX(paymentTransactionId) AS maxTransId, invoiceId FROM paymentTransactions GROUP BY invoiceId) recentTrans      ON recentTrans.invoiceId = i.invoiceId  LEFT JOIN paymentTransactions cts ON cts.paymentTransactionId = recentTrans.maxTransId AND cts.invoiceId = recentTrans.invoiceId WHERE i.invoiceId IN(130623, 3062)  GROUP BY i.invoiceId ORDER BY `invoiceId` desc;

На самом деле, для InvoiceID: 130623 нет платежной транзакции, но recentStatus возвращается как 1. Ожидается, что он вернет NULL.

Данные:

счета-фактуры:

 invoiceId  customerId amount
3062          1         40
130623        2         60

Платежные транзакции:

 paymentTransactionId, invoiceId, status
305                     3062        2
306                     3062        1

Ценю все ответы.

Спасибо

1. Не могли бы вы добавить образец данных, пожалуйста.

2. Добавлен образец данных. Пожалуйста, обновите

3. Ваш запрос не дает результата, который вы видите, и у вас есть группа, которая будет недействительной, если установлено значение only_full_group_by, я не могу воспроизвести вашу проблему db-fiddle.com

4. Это то, что происходит, когда вы выбираете неагрегированные столбцы, которые не отображаются в предложении GROUP BY .

5. @P.Salmon Но опция only_full_group_by отключена

Ответ №1:

Вы получаете неожиданные результаты, потому что в SELECT списке есть неагрегированные столбцы, которые не включены в GROUP BY предложение.

Но вам не нужен этот GROUP BY пункт:

 SELECT i.invoiceId, cts.paymentTransactionId maxTransId, cts.status as recentStatus  
FROM invoices i 
LEFT JOIN  (
  SELECT MAX(paymentTransactionId) AS maxTransId, invoiceId 
  FROM paymentTransactions 
  GROUP BY invoiceId
) recentTrans ON recentTrans.invoiceId = i.invoiceId  
LEFT JOIN paymentTransactions cts 
ON cts.paymentTransactionId = recentTrans.maxTransId AND cts.invoiceId = i.invoiceId 
WHERE i.invoiceId IN (130623, 3062)  
ORDER BY i.invoiceId ASC;

или с коррелированным подзапросом:

 SELECT i.invoiceId, pt.paymentTransactionId maxTransId, pt.status as recentStatus  
FROM invoices i LEFT JOIN paymentTransactions pt 
ON pt.invoiceId = i.invoiceId 
AND pt.paymentTransactionId = (
  SELECT MAX(paymentTransactionId)
  FROM paymentTransactions
  WHERE invoiceId = i.invoiceId  
)
WHERE i.invoiceId IN (130623, 3062)  
ORDER BY i.invoiceId ASC;

или, если ваша версия MySQL 8.0 , используйте CTE с ROW_NUMBER() оконной функцией:

 WITH pt AS (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY invoiceId ORDER BY paymentTransactionId DESC) rn
  FROM paymentTransactions
)
SELECT i.invoiceId, pt.paymentTransactionId maxTransId, pt.status as recentStatus 
FROM invoices i LEFT JOIN pt
ON pt.invoiceId = i.invoiceId AND pt.rn = 1 
WHERE i.invoiceId IN (130623, 3062)  
ORDER BY i.invoiceId ASC;

Посмотрите демонстрационную версию.

 > invoiceId | maxTransId | recentStatus
> --------: | ---------: | -----------:
>      3062 |        306 |            1
>    130623 |       null |         null