mysql - 我需要编写一个查询来显示每个州的员工计数列表

Question

将显示每个州的所有员 worker 数。我写的查询是:

Select count(distinct a.id)   
from address a   
Group by a.state;

是否有更优化的解决方案？

有两个表

CREATE TABLE `address` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `city` varchar(255) NOT NULL,
  `state` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4832 DEFAULT CHARSET=utf8;

CREATE TABLE `employee` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(255) NOT NULL,
  `lname` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `title` varchar(255) NOT NULL,
  `dob` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=58993 DEFAULT CHARSET=utf8;

Answer 1

您的查询不会产生正确的输出，因为 employee 和 address 表之间没有关系。

您应该向 employee 表添加一列，该列将引用 address 表中的 id:

ALTER TABLE employee
ADD COLUMN address_id INT NOT NULL;

然后，计算每个州的员工数量:

SELECT 
    a.state,
    COALESCE(COUNT(e.id), 0) AS employee_count
FROM address a
LEFT JOIN employee e
    ON e.address_id = a.id
GROUP BY a.state

<小时/>

此外，您还可以添加 FOREIGN KEY 约束:

ALTER TABLE employee
ADD FOREIGN KEY fk_address(address_id)
REFERENCES address(id)
ON DELETE NO ACTION
ON UPDATE NO ACTION;