mysql - 仅当两个或多个记录具有相同值时才连接两个表并分组

Question

这个问题已经有答案了:

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL? (22 个回答)

已关闭 4 年前。

我有 2 张 table :

manifests
id | customer | paid_order | price | payment_method

paid_orders
id | paid

JOIN paid_orders ON manifests.paid_order = paid_order_id

Let's assume this scenario:
There are 2 same customer but has different payment_method.

customer | paid_order | price | payment_method |  paid  |
    1    |      1     | 200   |        0       |  200   |
    1    |     NULL   | 100   |        1       |  NULL  |
    2    |     NULL   | 150   |        1       |  NULL  |

我只想GROUP BY customer而且还选择仅 payment_method的0 仅如果有两个相同的客户。

预期结果:

customer | paid_order | price | payment_method |  paid  |
    1    |      1     | 200   |        0       |  200   |
    2    |     NULL   | 150   |        1       |  NULL  |

Answer 1

一种方法是始终从一个或可能多个客户记录中选择 payment_method 具有最小值的记录:

SELECT m1.*
FROM manifest m1
INNER JOIN
(
    SELECT customer, MIN(payment_method) AS min_payment_method
    FROM manifest
    GROUP BY customer
) m2
    ON m1.customer = m2.customer AND m1.payment_method = m2.min_payment_method;

此逻辑应该有效，因为如果存在零的 payment_method ，则该记录将成为目标。否则，单值记录将成为目标。