duanmei1694
2015-03-03 19:46
浏览 327
已采纳

MySQL LEFT JOIN导致“重复”行

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...

图片转代码服务由CSDN问答提供 功能建议

我有“客户”和“计划”表,我想列出所有客户,无论他们是否有计划 。 我正在使用查询来执行此操作

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

我还希望看到带有客户名称的目标(plan.goal)。 只要客户没有计划或有单一计划,这就有效。 如果客户有两个或更多计划,那么我会获得与计划一样多的客户名称行。

我想要的是来自最新计划的customer.name和plan.goal。 我们可以假设plan.planid中的值更高是最新的计划。

我想我应该使用子查询和INNER JOINS一些如何,但我现在没有得到它.. 。

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • dongpu3898 2015-03-03 19:54
    已采纳

    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
    
    点赞 评论
  • dongyou6909 2015-03-03 19:57
    SELECT c.name, 
    ( SELECT p.goal
      FROM plan p
      WHERE p.customerid=c.customerid
      AND NOT EXISTS (  SELECT 'a'
                        FROM plan p2
                        WHERE p2.customerid = p.customerid
                        AND p2.planid > p.planId
                      )
    )
    FROM customer c
    
    点赞 评论
  • doulian1852 2015-03-03 20:32

    I think you should add a boolean/tinyint column to the plan table that says IsLatest or something like that. Then you could do:

    SELECT customer.name, plan.goal
    FROM customer
    LEFT JOIN plan ON plan.customerid=customer.customerid
    where testplan.islatest = 1 or testplan.islatest is null
    ORDER BY customer.name
    

    Also, I would stay away from the sub-query answers such as

    select a from (select b from c where e=f) where g=h
    

    as they don't often perform very well, besides being confusing to read.

    点赞 评论

相关推荐 更多相似问题