mysql - 从不同的表中获取总和和计数

Question

我猜这太本地化了，但我无法找到一种方法使其更通用(这可能就是为什么我无法在 Google 上找到答案的原因)。

我们有一个应用程序可以跟踪我们业务的联系人。这些联系人(联系人表)可以通过电话(Contact_Phone 表)或电子邮件(Contact_Email)联系。如果通过电话联系用户，代理会跟踪总秒数 (Contact_Phone.totalSeconds)。通过一个我无法控制的业务逻辑，电子邮件联系人被视为一秒钟。可以仅通过电子邮件、电话或两者联系用户。

我正在尝试生成一份报告，说明我们与每个用户联系所花费的时间，但我无法获得预期的结果。

表格:

CREATE  TABLE IF NOT EXISTS `Contact` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


CREATE  TABLE IF NOT EXISTS `Contact_Email` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `ContactId` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `contact_email_contact_idx` (`ContactId` ASC) ,
  CONSTRAINT `contact_email_contact`
    FOREIGN KEY (`ContactId` )
    REFERENCES `Contact` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

CREATE  TABLE IF NOT EXISTS `Contact_Phone` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `totalSeconds` INT NULL ,
  `ContactId` INT NULL ,
  PRIMARY KEY (`id`) ,
  INDEX `Contact_Phone_contact_idx` (`ContactId` ASC) ,
  CONSTRAINT `Contact_Phone_contact`
    FOREIGN KEY (`ContactId` )
    REFERENCES `Contact` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;

测试数据:

insert into Contact (id, name) values (1, 'Scott');
insert into Contact (id, name) values (2, 'Joe');

insert into Contact_Phone (totalSeconds, ContactId) values (10, 2);
insert into Contact_Phone (totalSeconds, ContactId) values (100, 2);

insert into Contact_Email (ContactId) values (1);
insert into Contact_Email (ContactId) values (1);
insert into Contact_Email (ContactId) values (2);

查询:

select 
name,
    (select sum(totalSeconds) from Contact_Phone where Contact_Phone.ContactId = Contact.id) 
    + 
    (select count(*) from Contact_Email where Contact_Email.ContactId = Contact.id)
from Contact;

预期结果:

Joe    111
Scott  2

实际结果:

Joe    111
Scott  null

谢谢

Answer 1

像这样使用摘要和 LEFT JOIN 操作怎么样？

SELECT Contact.name,
       COALESCE(p.seconds,0) + COALESCE(e.seconds,0) seconds
  FROM Contact.Name
  LEFT JOIN (
              SELECT ContactID AS id,
                     SUM(totalSeconds) AS seconds
                FROM ContactPhone
               GROUP BY ContactID
             ) p  ON Contact.id = p.id
  LEFT JOIN (
              SELECT ContactID AS id,
                     COUNT(*) AS seconds
                FROM ContactEmail
               GROUP BY ContactID
             ) e  ON Contact.id = e.id

LEFT JOIN 操作将保留结果行，其中一个或另一个“秒”计算结果为 NULL。并且，COALESCE 操作将阻止您的查询尝试对 NULL 值进行算术运算，这会产生 NULL。