MySQL INNER JOIN 的 3 个表的计数和总计 [英] MySQL INNER JOIN of 3 tables with count and totals

问题描述

我设置了以下示例数据库 -

<前>如果不存在公司"，则创建表(`id` int(10) 无符号 NOT NULL AUTO_INCREMENT，`company` varchar(75) 非空，主键(`id`))ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;插入`公司`(`id`，`company`)值(1, 'Acme Widget Company'),(2, 'Intrepid Inc.'),(3, 'Allied Corp.');如果不存在则创建表`companies_customers`(`id` int(11) NOT NULL AUTO_INCREMENT,`company_id` int(11) 非空，`customer_id` int(11) 非空，主键(`id`))ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;插入`companies_customers`(`id`、`company_id`、`customer_id`)值(1, 2, 1),(2, 2, 2),(3, 2, 4),(4, 1, 3),(5, 1, 1);CREATE TABLE IF NOT EXISTS `customers` (`id` int(10) 无符号 NOT NULL AUTO_INCREMENT，`firstname` varchar(25) 非空，`lastname` varchar(50) 非空，主键(`id`))ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;INSERT INTO `customers` (`id`, `firstname`, `lastname`) 值(1, '约翰', '史密斯'),(2, '苏', '琼斯'),(3, '大卫', '佛兰德斯'),(4, '凯西', '弗里曼');如果不存在订单"，则创建表(`id` int(10) 无符号 NOT NULL AUTO_INCREMENT，`customer_id` int(11) 非空，`amount` 十进制(10,0)非空，主键(`id`))ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;INSERT INTO `orders` (`id`, `customer_id`, `amount`) VALUES(1, 1, 500),(2, 3, 1000),(3, 1, 250),(4, 4, 800),(5, 4, 100);

我需要编写一个查询来检索所有公司名称的列表、每家公司的客户数量以及每家公司的客户订单总和，如下所示 -

<前>公司 客户总数 所有订单总数Acme Widget 公司 2 750无畏公司 3 1650联合公司 0 0

我几乎用下面的 SQL 解决了这个问题 -

<前>SELECT company AS 'Company', customersCount AS 'Total Customers', customerOrdersTotal AS 'All Orders Total'从( SELECT cc.customer_id, SUM(innerQuery.ordersTotal) customerOrdersTotalFROM (SELECT cu.id customerId, SUM(amount) ordersTotal来自客户 cu加入订单 o ON o.customer_id = cu.id按客户 ID 分组) 内部查询加入公司_客户 cc ON innerQuery.customerId = cc.customer_id按 cc.customer_id 分组) 内部_1右加入( SELECT cc.id, c.company, COUNT(*) customersCount来自公司 c加入公司_客户 cc ON c.id = cc.company_id按 c.id 分组) 内部_2ON inner_1.customer_id = inner_2.id

它不会打印没有客户或总数的公司(Allied).如此接近，我只需要朝着正确的方向轻推.谢谢.

解决方案

由于订单通过客户链接到公司，我认为您不需要执行两个单独的子查询并加入他们;相反，我认为你可以写:

SELECT Companies.company AS公司"，IFNULL(COUNT(DISTINCT Companies_customers.customer_id), 0) AS总客户数"，IFNULL(SUM(orders.amount), 0) AS "所有订单总数"来自公司剩下加入公司_客户ON company_customers.company_id = Companies.id剩下加入订单ON orders.customer_id = Companies_customers.customer_id团体由公司.id;

编辑添加:也就是说，我不得不说模式对我来说没有意义.客户和公司之间是多对多的关系——例如，John Smith 是 Acme Widget Company 和 的客户 Intrepid Inc. —但是订单只是客户的财产，不是公司的.这意味着，如果订单属于 John Smith，那么它必须both属于 Acme Widget Company 和Intrepid Inc..我不认为那是对的.我认为 orders 需要有一个 companies_customers_id 字段，而不是 customer_id 字段.

I have the following sample database set up -

CREATE TABLE IF NOT EXISTS `companies`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `company` varchar(75) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

INSERT INTO `companies` (`id`, `company`) VALUES
(1, 'Acme Widget Company'),
(2, 'Intrepid Inc.'),
(3, 'Allied Corp.');

CREATE TABLE IF NOT EXISTS `companies_customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `companies_customers` (`id`, `company_id`, `customer_id`) VALUES
(1, 2, 1),
(2, 2, 2),
(3, 2, 4),
(4, 1, 3),
(5, 1, 1);

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `firstname` varchar(25) NOT NULL,
  `lastname` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

INSERT INTO `customers` (`id`, `firstname`, `lastname`)   VALUES
(1, 'John', 'Smith'),
(2, 'Sue', 'Jones'),
(3, 'David', 'Flanders'),
(4, 'Kathy', 'Freeman');

CREATE TABLE IF NOT EXISTS `orders`  (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `amount` decimal(10,0) NOT NULL,
  PRIMARY KEY (`id`)
)ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

INSERT INTO `orders` (`id`, `customer_id`, `amount`) VALUES
(1, 1, 500),
(2, 3, 1000),
(3, 1, 250),
(4, 4, 800),
(5, 4, 100);

I need to write a query which retrieves a list of all company names, a count of the number of customers in each company, and a sum of the customers orders in each company, like this -

Company                 Total Customers All Orders Total
Acme Widget Company     2               750
Intrepid Inc.           3               1650
Allied Corp.            0               0

I nearly have it resolved with the following SQL -

SELECT company AS 'Company', customersCount AS 'Total Customers', customerOrdersTotal AS 'All Orders Total'
  FROM
  ( SELECT cc.customer_id, SUM(innerQuery.ordersTotal) customerOrdersTotal
    FROM (SELECT cu.id customerId, SUM(amount) ordersTotal
          FROM customers cu
          JOIN orders o ON o.customer_id = cu.id
          GROUP BY customerId
         ) innerQuery
    JOIN companies_customers cc ON innerQuery.customerId = cc.customer_id
    GROUP BY cc.customer_id
  ) inner_1
  RIGHT JOIN 
    ( SELECT cc.id, c.company, COUNT(*) customersCount
      FROM companies c
      JOIN companies_customers cc ON c.id = cc.company_id
      GROUP BY c.id
    ) inner_2
  ON inner_1.customer_id = inner_2.id

It does not print out the company (Allied) without a customer or total. So close, I just need a nudge in the right direction. Thanks.

解决方案

Since the orders are linked to the companies via the customers, I don't think you need to perform two separate subqueries and join them; rather, I think you can just write:

SELECT companies.company AS "Company",
       IFNULL(COUNT(DISTINCT companies_customers.customer_id), 0) AS "Total Customers",
       IFNULL(SUM(orders.amount), 0) AS "All Orders Total"
  FROM companies
  LEFT
  JOIN companies_customers
    ON companies_customers.company_id = companies.id
  LEFT
  JOIN orders
    ON orders.customer_id = companies_customers.customer_id
 GROUP
    BY companies.id
;

Edited to add: That said, I have to say that the schema doesn't really make sense to me. You have a many-to-many relationship between customers and companies — so, for example, John Smith is a customer of Acme Widget Company and of Intrepid Inc. — but then orders are just a property of the customer, not of the company. This means that if an order belongs to John Smith, then it necessarily belongs both to Acme Widget Company and to Intrepid Inc.. I don't think that can be right. Instead of having a customer_id field, I think orders needs to have a companies_customers_id field.

这篇关于MySQL INNER JOIN 的 3 个表的计数和总计的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！