MySQL左联接 [英] MySQL Left Joins

问题描述

好的，我想更清楚一点-我希望结果显示表acme中出现的所有名称"，而不是结果表中的计数(如果有的话).希望有道理吗?

OK, think I need to be clearer - I'd like the result to show all the 'names' that appear in the table acme, against the counts (if any) from the results table. Hope that makes sense?

有一个大问题，我的大脑无法正常工作.

Having a huge issue and my brain isn't working as it should.

我要做的就是在通过联接的单个语句中计算一个公共字段的行数.

All I want to do is, in a single statement via a join, count the number of rows for a common field.

   SELECT name, COUNT(name) as Count FROM acme
   SELECT name, COUNT(name) as Total FROM results

我确定应该是这样的……

I'm sure it should be something like this...

   SELECT acme.name, COUNT(acme.name) As Count, 
          COUNT(results.name) as Total 
   FROM acme 
   LEFT JOIN results ON acme.name = results.name 
   GROUP BY name 
   ORDERY BY name

但是它不能带回正确的计数.

But it doesn't bring back the correct counts.

想法，我要去哪里了...我知道，这将非常非常明显.

Thoughts, where am I going wrong...this, I know, will be very very obvious.

H.

推荐答案

从您的反馈中，您将获得所需的内容.您首先需要首先从"ACME"文件中获取唯一的名称/计数...然后将其连接到结果表以获取该记录的计数，否则，最终将得到笛卡尔计数结果.如果ACME的名称"X"是5倍，结果的"X"是20倍，则总数为100.下面的查询实际上将显示一行显示"X"，5、20的行，这就是您要查找的内容for ..(因为ACME中存在许多名称).

From your feedback, this will get what you want. You need to FIRST get unique names / counts from the "ACME" file first... THEN join that to the results table for count of records from that, otherwise, you would end up with a Cartesian result of counts. If ACME had Name "X" 5 times and Results had "X" 20 times, your total would be 100. The query below will actually result with a single row showing "X", 5, 20 which is what it appears you are looking for.. (for however many names exist in ACME).

如果ACME表中的名称在RESULTS表中不存在，我将更改为LEFT联接，它将不会从您的最终答案中删除它们

I've changed to a LEFT join in case there are names in the ACME table that DO NOT exist in the RESULTS table, it won't drop them from your final answer

select
      JustACME.Name,
      JustACME.NameCount,
      COALESCE( COUNT( * ), 0 ) as CountFromResultsTable
   from
      ( select a.Name
               count(*) as NameCount
           from
              acme a
           group by
              a.Name ) JustACME

      LEFT JOIN results r
         on JustACME.Name = r.Name
   group by
      JustACME.Name

这篇关于MySQL左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！