MySQL LEFT JOIN导致“重复”行

Question

I have tables "customers" and "plans" and I want to list all customers regardless they have a plan or not. I'm using query to do it

SELECT customer.name, plan.goal
FROM customer
LEFT JOIN plan ON plan.customerid=customer.customerid
ORDER BY customer.name

I also want to see the goal (plan.goal) with the customer name. This works as long as customer does not have a plan or has a single plan. If customer has two or more plans then I get as many rows of customer name as there are plans.

What I want is customer.name and plan.goal from the latest plan. We can assume the higher value in plan.planid is the latest plan.

I guess I should use sub queries and INNER JOINS some how but I just don't get it right now...

Answer 1

I would think something like this would work:

SELECT customer.name, plan.goal
FROM customer c
inner join plan p on c.customerId = p.customerId
inner JOIN (
    -- grabs the most recent plan per customer
    select max(planId) as planId, customerId
    from plan
    group by customerId
) maxPlan on p.planId = maxPlan.planId
UNION
-- handles customers w/o a plan
select customer.name, null
from customer c
left join plan p on c.customerId = p.customerId
where p.customerId is null
ORDER BY customer.name